|
|
|||||||||
|
|||||||||
|
|||||||||
| |
||
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
#1
|
|||
|
|||
|
Grouping by Date in DATETIME Field
Hi Guys,
I have a query like so: SELECT creationDate, COUNT(custID) AS totalRegistered FROM dbo.SecureSignInUser WHERE (CreationDate BETWEEN '11/1/2004' AND '11/30/2004' GROUP BY creationDate What I want, is it to break the days of November down so it gives me the creationDate in terms of 11/1/2004, 11/2/2004 etc with a count of how many people were registered on that day. e.g. 11/1/2004 - 3,604 11/2/2004 - 2,612 11/3/2004 - 1,543 11/4/2004 - 5,768 My problem is that creationDate is a DATETIME field so the date also has a time datepart so the results I get are as follows: 11/1/2004 00:00:00 - 604 11/1/2004 01:00:00 - 612 How do I do this so it is grouped only by the date and the time is totally ignored???? Thanking you for your help in advance. Breggs |
|
#2
|
|||
|
|||
|
SELECT convert(char(10), creationDate, 120), COUNT(custID) AS totalRegistered
FROM dbo.SecureSignInUser WHERE (CreationDate BETWEEN '11/1/2004' AND '11/30/2004' GROUP BY convert(char(10), creationDate, 120) |
![]() |
| Viewing: ASP Free Forums > Database > Microsoft SQL Server > Grouping by Date in DATETIME Field |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|