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

Question

I have data that is timestamped with a DATETIME column and I want to filter it down to the set of records where the DATETIME is between 9:30am and 5:30pm for any day. What's the best way to do this?

UPDATE: Changed because I need precision to the minute, not just hour. Sorry about that.

Answer 1

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

Answer 2

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...)

Alternatively:

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 make your @start_time and @end_time 1900-01-01 with the time needed.

That will exclude 05:00:00.000 PM, but you should be able to adjust if needed. Also keep in mind that it can't efficiently use indexes for this search. If the time of day is often going to be used for searching or calculations you might consider separating it out and keeping DATE and TIME as two columns.

Answer 3

You could always write it as a User Defined Function, particularly if you need to do this 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 so

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

Tested with the following in 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'

results in

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 4

Similar to above code, just add the DATEPART(mi, column) for your MINUTE requirement

However, note this will suffer in performance as functions on the columns will avoid using index (non-sargeable)

If you are using SQL 2008, they have DATE and TIME new data types that you can use to compare. If not, in SQL 2005, perhaps you can create computed columns for TIME portion and utilize it

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

Either 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)

alternatively:

   ...
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'

No index can be used for this any of these queries. To use an index, pre-compute the values some way or other and store them in an indexed column.

Answer 7

Concatenate hour and minutes and compare the 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 index. I cannot test, i post this with 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's 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 an index as we didn't put an expression on the left side of the where's condition.