GlenCloncurry GlenCloncurry - 10 months ago 78
SQL Question

SQL check time value between a range

I am trying to write a query which checks whether a variable is between a time range in separate columns in the database.. If it is, then I would like to return the row.

When I use the following I get the following result

declare @mytime datetime = '20160917 07:56'
select *
from OEEEvent oe
inner join RSBizWare.dbo.OEEConfigEvent ce on oe.lOEEConfigEventId = ce.lOEEConfigEventId
inner join RSBizWare.dbo.OEELOVCodeVal rs on oe.sStartVal = rs.sDescription and ce.lOEEIntRSSqlId=rs.lOEELOVCodeId
inner join RSBizWare.dbo.OEEStateConfig mms on rs.lMachineState = mms.lOEEStateConfigId

where tstart > @mytime

enter image description here

However, when I change the query to check whether the variable @mytime is between the time range, I get no result. The @mytime variable has been changed to 8am which we can previously see there was data there.

enter image description here

Thank you

Answer Source

Try by using Variable in where condition as following:

WHERE @mytime BETWEEN  tstart  AND tend 

And for addressing hour and minute only, it should work please try:

WHERE LEFT(@mytime, 17) BETWEEN LEFT(tstart, 17) AND LEFT(tend, 17)