How to write a where clause in SQL to filter a DATETIME column by time of day?

Question

I have data marked DATETIME and I want to filter it to a set of records where DATETIME is between 9:30 and 17:30 any day. What is the best way to do this?

UPDATE: Changed because I need accuracy per minute, not just an hour. Sorry.

Answer 1

You can always write it as a custom function, especially if you need to do it regularly

CREATE function dbo.timepart (@date as SMALLDATETIME) RETURNS SMALLDATETIME AS BEGIN RETURN @date - DATEADD(d, DATEDIFF(d,0,@date), 0) END 

and then use like this:

 SELECT * FROM table WHERE dbo.timepart(date_field) BETWEEN '9:30' AND '17:30' 

Tested with SQL Server 2005

 DECLARE @dates TABLE (date DATETIME) DECLARE @date DATETIME SET @date = '20 MAR 2009' WHILE @date < '22 MAR 2009' BEGIN INSERT INTO @dates VALUES(@date) SET @date = DATEADD(hour, 1, @date) END SELECT * FROM @dates WHERE dbo.timepart(date) BETWEEN '9:30' AND '17:30' 

leads to

 date ----------------------- 2009-03-20 10:00:00.000 2009-03-20 11:00:00.000 2009-03-20 12:00:00.000 2009-03-20 13:00:00.000 2009-03-20 14:00:00.000 2009-03-20 15:00:00.000 2009-03-20 16:00:00.000 2009-03-20 17:00:00.000 2009-03-21 10:00:00.000 2009-03-21 11:00:00.000 2009-03-21 12:00:00.000 2009-03-21 13:00:00.000 2009-03-21 14:00:00.000 2009-03-21 15:00:00.000 2009-03-21 16:00:00.000 2009-03-21 17:00:00.000 

Answer 2

As in the previous code, just add DATEPART (mi, column) for your MINUTE requirement

However, note that this will suffer from performance, as functions in columns will avoid using an index (without hyphenation)

If you are using SQL 2008, they have new DATE and TIME data types that you can use for comparison. If not, in SQL 2005, perhaps you can create calculated columns for the TIME part and use it

Answer 3

 SELECT * FROM table1 WHERE (DatePart(hh, DateColumn) >= 9 and DatePart(mm, DateColumn) >= 30) AND (DatePart(hh, DateColumn) <= 17 AND DatePart(mm, DateColumn) <= 30) 

Answer 4

 SELECT * FROM My_Table WHERE DATEPART(hh, some_date) BETWEEN 9 AND 16 OR (DATEPART(hh, some_date) = 17 AND DATEPART(mm, some_date) = 0...) 

As an alternative:

 SELECT * FROM My_Table WHERE DATEADD(dd, 1-DATEPART(dd, some_date), DATEADD(mm, 1-DATEPART(mm, some_date), DATEADD(yy, 1900-DATEPART(yy, some_date), some_date))) BETWEEN @start_time AND @end_time 

Just create @start_time and @end_time 1900-01-01 with the required time.

This excludes 05: 00: 00.000 PM, but you can adapt if necessary. Also keep in mind that it cannot use indexes effectively for this search. If the time of day is often used for searching or computing, you can consider separating it and storing DATE and TIME as two columns.

Answer 5

 SELECT * FROM your_table WHERE (DATEPART(hh, date_column) = 9 AND DATEPART(mi, date_column) >= 30) OR (DATEPART(hh, date_column) = 17 AND DATEPART(mi, date_column) <= 30) OR DATEPART(hh, date_column) BETWEEN 10 AND 16 

Answer 6

Or something like this:

  ... WHERE (DATEPART(hh, some_date) BETWEEN 10 AND 16) OR (DATEPART(hh, some_date) = 9 AND DATEPART(mi, some_date) >= 30) OR (DATEPART(hh, some_date) = 17 AND DATEPART(mi, some_date) <= 30) 

as an alternative:

  ... WHERE LEFT(CONVERT(VARCHAR, some_date, 8), 5) >= '09:30' AND LEFT(CONVERT(VARCHAR, some_date, 8), 5) <= '17:30' 

or even:

  ... WHERE LEFT(CONVERT(VARCHAR, some_date, 8), 5) BETWEEN '09:30' AND '17:30' 

For this, none of these queries can be used by any index. To use an index, pre-compute the values ​​one way or another and save them in the indexed column.

Answer 7

Combine hour and minute and compare range.

 with Result as ( select [Time] = cast(cast(datepart(hh, AuditDate) as varchar) + cast(datepart(mi, AuditDate) as varchar) as int), * from AuditTable ) select * from Result where [Time] between 930 and 1730 

Answer 8

 Select * from tbl where ts between convert(datetime,convert(varchar,ts, 101)) + convert(datetime,'9:30 am') And Convert(datetime,convert(varchar,ts,101)) + convert(datetime,'5:30 pm') 

This, I believe, will use the index. I can not verify, I am sending this using ipod

[EDIT from my computer]

 create function dbo.DaysTime(@date as datetime, @time datetime) returns datetime as begin /* datediff approach on scraping the time part of datetime: inspired by Russ Cam */ return dateadd(d, datediff(d, 0, @d), 0)/*the date day*/ + @time end select * from tbl where ts between dbo.DaysTime(ts, '9:30 AM') and dbo.DaysTime(ts, '5:30 PM') 

This will still use the index, since we do not put the expression on the left side of the where clause.