CODEWITHSUNDEEP
sql-servert-sql
Mike U
Mike U

Reputation: 3061

SQL Server Between Time

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

Upvotes: 1

Views: 146

Answers (2)

Alex Kudryashev
Alex Kudryashev

Reputation: 9470

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)

Upvotes: 1

Alex
Alex

Reputation: 5157

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.

Upvotes: 1

Related Questions