CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
Lacrymae
Lacrymae

Reputation: 157

Checking is time in specific time range

I want make a decision with CASE Statement on time format. But can't make a right decision with time formats because my end time of time range is smaller than start time. Let me explain with code;

DECLARE @Startdate datetime
DECLARE @START_TIME time(0)
DECLARE @END_TIME time(0)

SET @Startdate='2020-04-15 16:00:00.000'
SET @START_TIME='15:30:00'
SET @END_TIME='01:29:59'

select 
CASE WHEN CAST(@Startdate as time(0)) between @START_TIME and @END_TIME  THEN 1 ELSE 0 END

Also i have table in DB just like below;

ID  START_TIME  END_TIME
1   05:30:00    15:29:59
2   15:30:00    01:29:59

Just i want to check time of coming datetime value exactly fit with which ID. I don't care date part. How can i achieve this?

Thank you in advance.

Upvotes: 1

Views: 57

Answers (1)

GMB
GMB

Reputation: 222432

One method is:

select case 
    when 
        (
            @end_time >= @start_time 
            and cast(@startdate as time(0)) >= @start_time 
            and cast(@startdate as time(0)) <= @end_time  
        )
        or (
            @end_time < @start_time 
            and (
                   cast(@startdate as time(0)) >= @start_time 
                or cast(@startdate as time(0)) <= @end_time
            )
        )           
    then 1 
    else 0 
end

Upvotes: 1

Related Questions