CODEWITHSUNDEEP
sqlsql-serverdatetime
tarzanbappa
tarzanbappa

Reputation: 4958

SQL Check Current time is between two DATETIME columns

I need to check whether the current time is between two datetime column values.

And I only need to check the time is between the range and I don't want to check the date.

I know how to check a date is exists between a daterange like below

SELECT
    *
FROM 
    Table1 T
WHERE 
    CAST(GETDATE() AS DATE) BETWEEN T.StartDate AND T.EndDate

We have stored start date and end date as folllows..

StartDate - 1900-01-01 08:00:00.000
EndDate   - 1900-01-01 19:00:00.000

Is there something similar to this to check whether the time is exists in a date range?

Upvotes: 1

Views: 6616

Answers (4)

Ravi
Ravi

Reputation: 1172

if you only want to check time 

SELECT  *
FROM Table1 T
WHERE CAST(GETDATE() AS TIME) BETWEEN cast(T.StartDate as TIME) AND cast(T.EndDate as TIME)

Upvotes: 7

Squirrel
Squirrel

Reputation: 24763

CONVERT() to TIME and then back to DATETIME and compare

WHERE CONVERT(DATETIME, CONVERT(TIME, GETDATE()))
      BETWEEN T.StartDate AND T.EndDate

Upvotes: -1

tarzanbappa
tarzanbappa

Reputation: 4958

It worked when I tried like below

SELECT *
FROM Table1 T
WHERE CAST(GETDATE() AS TIME) BETWEEN CAST(T.StartDate AS TIME) AND CAST(T.EndDateAS TIME)

Upvotes: 1

Serg
Serg

Reputation: 22811

Something like this I guess

declare @d1 datetime ='20171220 10:00'
   , @d2 datetime = '20171220 12:00'
   , @t time ='11:00';
select 'Yes' where @t between cast(@d1 as time) and cast(@d2 as time);

Upvotes: 1

Related Questions