CODEWITHSUNDEEP
sqlsql-serverdatetimedatesum
Jeremy F.
Jeremy F.

Reputation: 1868

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 and 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

Upvotes: 2

Views: 2941

Answers (6)

Ian Nelson
Ian Nelson

Reputation: 58753

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.

Upvotes: 0

smirkingman
smirkingman

Reputation: 6368

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)

Upvotes: 0

marc_s
marc_s

Reputation: 754478

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)

Upvotes: 3

Ben Gribaudo
Ben Gribaudo

Reputation: 5147

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

Upvotes: 1

Daniel DiPaolo
Daniel DiPaolo

Reputation: 56390

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

Upvotes: 0

code master
code master

Reputation: 2026

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

Upvotes: 0

Related Questions