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