How do I check if the current time is between two times in SQL?

Question

This isn't working:

IF EXISTS (( SELECT  1
            FROM    dbo.SalesOrder )
   AND (GETDATE() BETWEEN '07:00:00' AND '16:00:00'))
   BEGIN
         PRINT 'yes!'
   END

I want to do:

If something exists in the SalesOrder table AND the current time is between 7am and 4pm, then print 'YES'

I'm assuming I might have to do a conversion there but I'm unsure on how to do it properly.

Anyone care to give me a hand?

I fixed it. All I'm trying to do is 1. check if someone exists in table A and 2. if the current time is between 7am and 4pm. 1 and 2 are completely different things. — JJ., Dec 16 '13 at 19:44
@Ben Did someone figure out this is for SQLServer? Or is this still generic/should work on all DBs? — , Dec 16 '13 at 19:47

juergen d · Accepted Answer · 2013-12-16T19:46:46.223

12

How about

SELECT top 1 'yes'
FROM    dbo.SalesOrder
WHERE datepart(hour, GETDATE()) BETWEEN 7 and 16

SQLFiddle demo

edited Dec 16 '13 at 19:46

answered Dec 16 '13 at 19:41

juergen d

201,996
37
293
362

Doesn't `GETDATE()` return a datetime? You're comparing it to two strings. How will that work? – Ben Dec 16 '13 at 19:43
This isn't working, unfortunately. – JJ. Dec 16 '13 at 19:44
convert(time,getdate()) needed? – sam yi Dec 16 '13 at 19:44
`TIME(GETDATE())` ? Although that may just work on MySQL. – Dec 16 '13 at 19:45
2

Time after 1600 hours will also be considered here, so this will match from 7:00:00 to 16:59:59 – Neo Aug 12 '15 at 08:14

score 3 · Answer 2 · edited May 23 '17 at 12:01

3

If you are trying to compare datetimes for the same day, you will need to remove the time component of GETDATE(), then add X hours. There are a few other conversions here.

IF EXISTS (( SELECT  1
            FROM    dbo.SalesOrder )
   AND (GETDATE() BETWEEN 
                      DATEADD(hh,7,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime))) AND DATEADD(hh,16,(CAST(FLOOR(CAST(GETDATE() as FLOAT)) AS DateTime)))))
   BEGIN
         PRINT 'yes!'
   END

edited May 23 '17 at 12:01

Community

1
1

answered Dec 16 '13 at 19:48

StingyJack

19,041
10
63
122

Can you say `CAST(GETDATE() as TIME)`? – Dec 16 '13 at 19:52
Dunno, have used the CAST/FLOOR/CAST for a while because it has always been the faster method. Your approach may be as good or better (its a heck of a lot shorter). Any idea if it is? – StingyJack Dec 16 '13 at 19:58
I couldn't get an actual compare of two SQL Server `TIME` objects to work unfortunately. Even MSDN recommends turning them into float. – Dec 16 '13 at 20:58
I don't have a use the TIME data type as of yet. Everything still in DATETIME/DATETIME2 for me. – StingyJack Dec 16 '13 at 22:33

How do I check if the current time is between two times in SQL?

2 Answers2

SQLFiddle demo