Group By with date only with a DateTime Date Type? 1

[EchoAlertcom]

Hello,

How do I Group By with date only with a DateTime Data Type?

SELECT Count(*) as GrossSubmits, SignupDate
FROM dbo.Leads where SignupDate BETWEEN '01-AUG-06' AND '14-AUG-06' Group By SignupDate

Currently this breaks out each time as a different grouping. I just want all the records for each date as one group.

Thank you,
Steve

 

[gmmastros]

This... DateAdd(day, DateDiff(Day, 0, getdate()), 0) will remove the time from a datetime value. So...

SELECT Count(*) as GrossSubmits, 
       DateAdd(day, DateDiff(Day, 0, SignupDate), 0) As SignupDate
FROM  dbo.Leads 
where SignupDate BETWEEN '01-AUG-06' AND '14-AUG-06' 
Group By DateAdd(day, DateDiff(Day, 0, SignupDate), 0)

You do realize that since there is a time component, no records will be returned for 14-AUG-06 unless the time is at midnight.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EchoAlertcom]

ah, no i didn't realize that. So this date range would only bring back Aug 1 through Aug 13 (and any from Aug 14 that are timestamped midnight)?

Thank you for your help (and your fast reply).

Warmest Regards,
Steve

 

[gmmastros]

Yes. Because of the time component. So... it's time to mention the 'best practice' bethod for bringing back dates. If you do this wrong, SQL Server won't be able to use an index (if it exists) on the date column. Indexes allow you to retrieve data faster, so obviously you would want to write your queries in a way that allows the indexes to be used.

For example, you could use the 'trick' I showed earlier for stripping the time from the field for use within the where clause.

[tt][blue]Where DateAdd(day, DateDiff(Day, 0, SignupDate), 0) between '01-AUG-06' and '14-AUG-06'[/blue][/tt]

This is the BAD way because it will likely cause a table scan and your performance will suffer.

The better way...

Where SignupDate >= '01-AUG-06'
      And SignupDate < '15-AUG-06'

Notice that we bumped the 'end date' by 1 day but use LESS THAN so that any records with 15-AUG-06 will NOT be returned, but all records on the 14th will. Assuming you have an index on the SignupDate field, this last query will use it.

Also notice that we no longer use the between operator because the end date must be the 15th, but you don't want records that occur on the 15th at midnight.

Hope this helps.



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[EchoAlertcom]

Hi George,

Thank you. I appreciate the time you took with the great explanation.

Warmest Regards,
Steve