How Do I Query for "Per Day" results for a given date range in MS SQL?

Question

Sorry if this has been asked before. I tried looking through the related questions and didn't find anything that I thought was relevant.

Anywho, I would like to build a query that will pull the number of rows that were created on a given date range and time frame. In other words. the date range would be for a given month, but I only want the rows that were created during a given time (say from 08:00 - 17:00) for each day throughout that month.

Is this easily possible? I was thinking subqueries for each day, but didn't know if there was an easier way.

TYIA

Answer 1

This would give you what you want, I think. You could make a stored procedure or function out of it and pass in the start/end dates and start/end hour (now fixed). Note the strictly less than on the end date. You could also use less than or equal to '8/31/2009 11:59:59'.

select count(*),
       datepart(year,created) as [year],
       datepart(month,created) as [month],
       datepart(day,created) as [day]
from table
where created >= '8/1/2009'
      and created < '9/1/2009'
      and datepart(hour,created) >= 8
      and datepart(hour,created) <= 17
group by datepart(year,created), datepart(month,created), datepart(day,created)

Answer 2

Use the datepart() function to test for the hours you are interested in.

For example:

select 1,
    datepart(hour,getDate())
where datepart(hour,getDate()) >= 8 and datepart(hour,getDate()) < 17

Answer 3

Can do something like this this does it for range of hours(12PM - 1PM) and days(today to 10 days ago):

select * from table where DateColumn < GetDate() and DateColumn > (GetDate() - 11) and DatePart(hh, DateColumn) >= 12 and DatePart(hh, DateColumn) <= 13

Answer 4

Why not just do this?

... WHERE MONTH(TheDateColum) = @Month AND HOUR(TheDateColum) >= 8 AND HOUR(TheDateColumn) <= 17

Hm... they beat me to it.