SQL Server: Count based on date and two different times

OS - WindowsXP

SQL Server Management Studio 2008 R2

I am trying to get a count of the number of projects based on the release date, NOT date and TIME. However, some projects have the same release date but a different time a开发者_高级运维nd are treated as a separate project. As long as the project has the same date, I want it to be counted.

'Releasedate' on the server is a "datetime"

Select ProjectName, count(ProjectName) as Count, (Releasedate)
From DBTable Where Releasedate >= convert(nvarchar,(getdate())) 
Group by   projectname,releasedt

Current Results:

 ProjectName       Count        Releasedate

 Project_Nm_1      1             2010-03-27 00:00:00
 Project_Nm_1      1             2010-03-27 08:00:00
 Project_Nm_2      1             2010-03-27 00:00:00
 Project_Nm_2      1             2010-03-27 08:00:00

I would like to see:

 Project_Nm_1      2             2010-03-27
 Project_Nm_2      2             2010-03-27 

---

SQL Server 2008 introduced the new DATE datatype which does exactly what you're looking for - handle only the date, without any time. So just CAST your field to DATE and you should be fine:

SELECT 
   ProjectName, COUNT(ProjectName) as Count, CAST(Releasedate AS DATE)
FROM 
   dbo.DBTable 
WHERE 
   CAST(Releasedate AS DATE) >= CAST(GETDATE() AS DATE)
GROUP BY 
   projectname, CAST(ReleaseDate as DATE)

---

To group by date only, try using the CONVERT function:

GROUP BY projectname, CONVERT(nvarchar, Releasedate, 101)

You'll want to use the same CONVERT function call in the select column list so that query's output shows just the date, as well.

Take a look at: http://codingforums.com/showthread.php?t=56536

---

You can get the date portion of the ReleaseDate datetime using

DateAdd(dd, 0, DateDiff(dd, 0, ReleaseDate))

Therefore your query becomes

SELECT 
    ProjectName,
    Count(ProjectName) as Count,
    DateAdd(dd, 0, DateDiff(dd, 0, ReleaseDate)) as ReleaseDate
FROM
    DBTable
WHERE
    ReleaseDate >= getdate()
GROUP BY
    ProjectName,
    DateAdd(dd, 0, DateDiff(dd, 0, ReleaseDate))

If you find yourself stripping the time from datetimes frequently, then encapsulate it in a UDF.

---

Select 
    ProjectName, count(ProjectName) as CountProjects, Releasedate
From 
    DBTable 
Where 
    Releasedate >= convert(nvarchar, getdate()) 
Group by   
    projectname,releasedt
Order by 
    CountProjects desc

P.S. Don't use built-in functions while choosing alias of any column

---

You could simply GROUP BY the parts of the date you want using DATEPART

SELECT
    ProjectName,
    Count(*),
    DATEPART(year, Releasedate) as ReleaseYear,
    DATEPART(month, Releasedate) as ReleaseMonth,
    DATEPART(day, Releasedate) as ReleaseDay
FROM
    DBTable 
WHERE
    Releasedate >= convert(nvarchar,(getdate()))
GROUP BY
    ProjectName,
    DATEPART(year, Releasedate),
    DATEPART(month, Releasedate),
    DATEPART(day, Releasedate)

I'll leave combining those parts into one field as an exercise for you if you want, but this will ignore the time portion of Releasedate when grouping

---

To explain:

declare @dt datetime
set @dt = '2010/12/22 12:34:56'
print @dt
print convert(char(8), @dt, 112)

Result:

Dec 22 2010 12:34PM
20101222

So, use

GROUP BY convert(char(8), releasedt, 112)