SQL Check Current time is between two DATETIME columns

Question

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?

Hmm I don't understand your question. Isn't the example you've shown exactly what you're asking for? — hage, Dec 20 '17 at 09:14
The very same way you would do with date values, just using DATETIME columns instead. — Pred, Dec 20 '17 at 09:14
Then how can be a TIME value between DATE values? How can be 12:54:53 between 2017-01-01 and 2017-05-01 ? — Pred, Dec 20 '17 at 09:17

score 7 · Accepted Answer · edited Dec 20 '17 at 10:54

7

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)

edited Dec 20 '17 at 10:54

tarzanbappa

4,930
22
75
117

answered Dec 20 '17 at 09:26

Ravi

1,157
1
9
19

score 1 · Answer 2 · answered Dec 20 '17 at 09:23

1

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

answered Dec 20 '17 at 09:23

Serg

22,285
5
21
48

score 1 · Answer 3 · answered Dec 20 '17 at 09:26

1

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)

answered Dec 20 '17 at 09:26

tarzanbappa

4,930
22
75
117

1

if can ensure the date is always `1900-01-01` then you don't need to CAST() or CONVERT() StartDate to `TIME`. This will avoid unnecessary scan and faster – Squirrel Dec 20 '17 at 09:31

Squirrel · Answer 4 · 2017-12-20T09:29:42.717

-1

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

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

edited Dec 20 '17 at 09:29

answered Dec 20 '17 at 09:19

Squirrel

23,507
4
34
32

StartDate = '01 jan 2017 17:54' and EndDate = '02 jan 2017 00:01' how this work? – Stanislav Kundii Dec 20 '17 at 09:22
the OP stated the date is '1900-01-01' – Squirrel Dec 20 '17 at 09:23

SQL Check Current time is between two DATETIME columns

4 Answers4

Linked