Querying a smalldatetime's date and time seperately in SQL server?

Question

Imagine a table that has two fields, a smalltimedate and an int and about 1000 rows of data. What I'm attempting to do in query is to find the average of the INT field for rows between 3/3/2010 - 3/13/2010 and only if the entry is between 6:00am - 11:00pm.

I tried

between '2010-03-03 06:00 AND 2010-03-13 23:00'

However that only restricts that very beginning and end times. I could do this with a loop but I'm going to need to have the same query run over much larger date ranges and this will quickly eat server resources. Is there a way to query date and time seperately?

Answer 1

Select ...
From Table
Where DateCol >= '20100303'
    And DateCol < '20100314'
    And DatePart(hh,DateCol) Between 18 And 23

Note that I use strictly less than in the comparison to 20100314 so it returns anything before 2010-03-14 midnight.

EDIT Realized you said PM and not AM.

ADDITION In SQL, the statement "Foo Between DateA And DateB" is translated into "Foo >= DateA and Foo <= DateB". Notice the second part of that statement is less than or equal to to DateB. In our query, we want to include everything on 2010-03-13 all the way through midnight. I achieve that by restricting my search to values strictly less than the day after I want to search.

With respect to the hour, DatePart(hh, DateVal) will return the hour of the day using the 24 hour clock. So 6 PM is really 18:00 hours. If we want between 6 PM and 11 PM we really want between 18:00 and 23:00.

ADDITION It occurs to me that there is a small problem in my original solution. The system will return values whose time is anywhere during the 11 o'clock hour (e.g. 11:01 PM, 11:30 PM, 11:50 PM etc.). Here is another solution that would solve that:

Select ...
From Table
Where DateCol >= '20100303'
    And DateCol < '20100314'
    And DateAdd(day, -DateDiff(Day, 0, [DateCol]), [DateCol]) Between '18:00' And '23:00'

Basically, I'm using the DateDiff and DateAdd functions to strip off the Date portion of the value and then compare against the values we want.

Answer 2

Create a separate computed column as datepart(hour, datecolumn) * 60 + datepart(minute, datecolumn), or only hour you don't need minute resolution. Persist the computed column to be able to index it and then you can efficiently seek ranges on time alone. In SQL 2008 there are some improvements (new TIME type).