SQL Server Between Time

Question

I am looking for an elegant way to select records that fall between a start and end time.

I have a datetime field that hold the date and time of the record. I can get the time by CAST(fieldname as time).

A BETWEEN does not work if you are looking for records that occurred between 22:00 and 03:00

Ideally I would like to use a CASE in the WHERE clause

CASE 
   WHEN @Start < @End THEN CAST(fieldname as time) BETWEEN @Start AND @END 
   ELSE (CAST(fieldname as time) > @Start OR CAST(fieldname as time) < @End)
END

Any sugestions how I could do this in a single elegant way.

Thanks

Actually `BETWEEN` should be what you are using IMO. If you also had dates associated with those times, then `BETWEEN` would do what you want. — Tim Biegeleisen, Jun 13 '16 at 03:14
No, I what to get all results that occured between the start and end time regardless of date — Mike U, Jun 13 '16 at 03:17
http://stackoverflow.com/questions/9840170/select-statement-filtering-by-time-of-day — Jim Hewitt, Jun 13 '16 at 03:20
`DATEPART(hour, CAST(fieldname as time)) >= 22 OR DATEPART(hour, CAST(fieldname as time)) <= 3` ? — Tim Biegeleisen, Jun 13 '16 at 03:23
That does not address the issue when Start = 22:00 and the end is the next day 03:00 — Mike U, Jun 13 '16 at 03:24
A `case` expression cannot return a boolean value. Change it to `and` the conditions and then return a `0` or `1`, then test for the resulting value after the `end`. [This](http://stackoverflow.com/questions/10256848/can-i-use-case-statement-in-a-join-condition/10260297#10260297) may provide a clue. — HABO, Jun 13 '16 at 03:26
if you want all result between start and end time regardless of date then you should give us few example.It is little confusing..Explain us how date do not matter.I thought start time is always less than end time. — KumarHarsh, Jun 13 '16 at 03:51

Alex · Accepted Answer · 2016-06-13T04:02:44.053

Elegant way:

SELECT *, CAST(datetimeFiled as time)
FROM #Test
WHERE
    ( @Start < @End AND ( @Start < CAST(datetimeFiled as time) AND CAST(datetimeFiled as time) < @End ))
    OR
    ( @Start > @End AND ( @Start < CAST(datetimeFiled as time) OR CAST(datetimeFiled as time) < @End ))

CASE WHEN way:

SELECT *, CAST(datetimeFiled as time),
    ( CASE WHEN @Start < @End THEN 0 WHEN @Start > @End THEN 1 END ) 
FROM #Test
WHERE 1 = (
        CASE
            WHEN @Start < @End THEN ( SELECT 1 WHERE ( @Start < CAST(datetimeFiled as time) AND CAST(datetimeFiled as time) < @End ))
            WHEN @Start > @End THEN ( SELECT 1 WHERE ( @Start < CAST(datetimeFiled as time) OR CAST(datetimeFiled as time) < @End ))
        END )

Sample data:

CREATE TABLE #Test( datetimeFiled DATETIME )
INSERT INTO #Test
VALUES
( '2016-06-13 13:11:00' ),
( '2016-06-12 23:11:00' ),
( '2016-06-12 23:00:00' ),
( '2016-06-13 14:00:00' )

DECLARE @Start TIME, @End TIME
SET @Start = '23:01:00'
SET @End = '13:25:00'

You can change < to <= and > to >= to make it inclusive.

Alex Kudryashev · Answer 2 · 2016-06-13T04:19:47.460

The idea of solution is to move times to the same day. It looks like this:

declare @startTime time='22:00'
       ,@endTime time='2:00'
       ,@shift int
select @shift= case when @startTime>@endTime then DATEDIFF(ss,@startTime,'23:59:59')+ 1 
               else 0 end 

select  cast(dateField as time) timeField, something
from table1
where dateadd(ss,@shift, cast(dateField as time)) between
     dateadd(ss,@shift,@startTime) and  dateadd(ss,@shift,@endTime)

Or if you wish in a single query:

declare @startTime time='22:00'
       ,@endTime time='2:00'

;with s as (
select case when @startTime>@endTime then DATEDIFF(ss,@startTime,'23:59:59')+ 1 
               else 0 end shift
)
select  cast(dateField as time) timeField, something
from table1 cross join s
where dateadd(ss,s.shift, cast(dateField as time)) between
     dateadd(ss,s.shift,@startTime) and  dateadd(ss,s.shift,@endTime)

My two cents: The first query can be further improved by pre-"shifting" @startTime and @endTime varibles so that the `WHERE` looks a bit cleaner. — Alex, Jun 13 '16 at 04:07

SQL Server Between Time

2 Answers2