SQL Server 2008 Group results in 24 hour range (from 10:01:00am to 10:00:59am)

Question

This question is related to SQL Server 2008.

I have a range of dates and need to count all dates within a 24 hour period between 10:01:00 AM and 10:00:59 AM.

I have the loop response ready to go for each day, but I can't figure out the right SQL statement to set the start and end times in the 24 hour range. Can someone please assist?

Current SQL Statement

SELECT 
    CONVERT(CHAR(8), DateAdd(hour, 10, EndDate), 10) as DailyDate, 
    COUNT(*) As Total
FROM 
    EndDate
WHERE 
    DateAdd(hour, 10, EndDate) > getDate()
    AND DateAdd(hour, 10, EndDate) < DateAdd(day, 10, getDate())
GROUP BY 
    CONVERT(CHAR(8), DateAdd(hour, 10, EndDate), 10)
ORDER BY 
    CONVERT(CHAR(8), DateAdd(hour, 10, EndDate), 10)

Desired Output

DailyDate - Total  
06-28-14 - ? *(should be 6)*  
06-29-14 - ? *(should be 13)*  
06-30-14 - ? *(should be 15)*  
07-01-14 - ? *(should be 8)*  
07-02-14 - ? *(should be 1)*

SOURCE

EndDate  
6/28/2014 8:23:00 PM  
6/29/2014 12:37:00 AM  
6/29/2014 2:56:00 AM  
6/29/2014 9:31:00 AM  
6/29/2014 10:00:00 AM  
6/29/2014 10:00:00 AM  

6/29/2014 10:01:00 AM  
6/29/2014 11:30:00 AM  
6/29/2014 1:09:00 PM  
6/29/2014 1:47:00 PM  
6/29/2014 6:01:00 PM  
6/29/2014 11:02:00 PM  
6/29/2014 11:33:00 PM  
6/30/2014 1:12:00 AM  
6/30/2014 3:37:00 AM  
6/30/2014 5:23:00 AM  
6/30/2014 5:24:00 AM  
6/30/2014 6:25:00 AM  
6/30/2014 10:00:00 AM  

6/30/2014 10:01:00 AM  
6/30/2014 10:02:00 AM  
6/30/2014 10:02:00 AM  
6/30/2014 11:11:00 AM  
6/30/2014 12:04:00 PM  
6/30/2014 5:15:00 PM  
6/30/2014 6:08:00 PM  
6/30/2014 9:51:00 PM  
6/30/2014 10:45:00 PM  
7/1/2014 12:14:00 AM  
7/1/2014 12:21:00 AM  
7/1/2014 2:32:00 AM  
7/1/2014 2:58:00 AM  
7/1/2014 6:30:00 AM  
7/1/2014 10:00:00 AM  

7/1/2014 12:40:00 PM  
7/1/2014 2:35:00 PM  
7/2/2014 12:04:00 AM  
7/2/2014 12:36:00 AM  
7/2/2014 2:34:00 AM  
7/2/2014 5:03:00 AM  
7/2/2014 6:14:00 AM  
7/2/2014 10:00:00 AM  

7/2/2014 10:01:00 AM

My first step would be to create a derived result that normalized the times to days, e.g. `select DateAdd(hour, -10, EndDate) as normDate, * from EndDate` .. then inspect the offsets and, if correct, use the derived query. (I believe the offset should be -10 hours, not +10 hours; using a derived relation will easily make this apparent - plus it will cut down on all the repeated typing.) — user2864740, Jun 25 '14 at 01:55
After the normalization, consider http://stackoverflow.com/questions/538412/t-sql-to-trim-a-datetime-to-the-nearest-date (which can itself be moved into the derived table suggested) — user2864740, Jun 25 '14 at 02:00
I was able to start the range at 10:01am by subtracting 601 minutes (10h 1m). I replaced DateAdd(hour, 10, EndDate) with DateAdd(minute, -601, EndDate) — Rick, Jul 08 '14 at 05:53

score 1 · Answer 1 · answered Jun 25 '14 at 02:16

Just subtract 10 hours from the results. Assuming that EndDate is datetime, you can do this by subtracting 10.0/24 days:

SELECT  CONVERT(CHAR(8), EndDate - 10.0/24, 10) as DailyDate, COUNT(*) As Total
FROM EndDate
WHERE DateAdd(hour, 10, EndDate) > getDate() AND
      DateAdd(hour, 10, EndDate) < DateAdd(day, 10, getDate())
GROUP BY CONVERT(CHAR(8), EndDate - 10.0/24, 10)
ORDER BY DailyDate;

The issue with your query is that you are adding 10 hours rather than subtracting 10 hours, so you could just modify your query as well and use -10 for the dateadd().

SQL Server 2008 Group results in 24 hour range (from 10:01:00am to 10:00:59am)

1 Answers1