SQLTeam.com | Weblogs | Forums

Same Date Range for a Year

I'm trying to get data from 01-01-2021 to 12-31-2021 from 9:00:00 to 15:00:00 everyday of the year. I'm struggling to figure out how to only print that date range everyday within SQL. I can get all the days to print, but don't have the correct level of knowledge to decipher how to print that time range everyday for 2021.


01-01-2021 9:00:00 to 01-01-2021 15:00:00
01-02-2021 9:00:00 to 01-02-2021 15:00:00
01-03-2021 9:00:00 to 01-03-2021 15:00:00

For the whole year

Not a lot to go on there, but I'll assume you have a datetime column in the table that you can query against (rather than separate date and time columns).

FROM dbo.your_table_name
WHERE datetime_column >= '20210101' AND datetime_column < '20220101' AND
    DATEPART(HOUR, datetime_column) BETWEEN 9 AND 14 --(Note1)

Note1: Not 15 because that would include everything in the 3PM hour. Realistically your query should include all of 3PM or none of it.


The first part of the WHERE -- comparing only the dates -- is to help with performance. If the table has an index on that column, the first clause will make access more efficient than just checking time alone.

Actually, for best performance, it's very possible that table should be clustered first on that datetime, in which case the WHERE on the date will really help performance. However, the table is probably not actually clustered first on that date -- even if that would be best -- so you may have to rely on a nonclustered index, which is more "iffy" if SQL can use it or not to satisfy the query.