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

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?

Answer 1

How about

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

SQLFiddle demo

Answer 2

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