CODEWITHSUNDEEP
sqlsql-server
GlenCloncurry
GlenCloncurry

Reputation: 517

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

Upvotes: 0

Views: 431

Answers (4)

qxg
qxg

Reputation: 7036

When you want to express BETWEEN, it's starttime < yourtime < endtime. It equals to

startTime < yourTime AND yourTime < endTime

while you write statement in opposite way

startTime > yourTime and yourTime > endTime

So change where clause to

tstart < @mytime AND @mytime < tend

You can use BETWEEN operator, but please notice BETWEEN operator include both range. See if it satisfy your requirement.

Upvotes: 0

Unnikrishnan R
Unnikrishnan R

Reputation: 5031

Change your condition like below. In your case the second condition will not satisfy the data range (tend >@mytime)

       WHERE  tstart >@mytime and @mytime <tend

Upvotes: 0

Shushil Bohara
Shushil Bohara

Reputation: 5656

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)

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93754

Use the below code to trim seconds from your datetime column

Select DATEADD(MINUTE, DATEDIFF(MINUTE, 0, yourcolumn), 0)

something like this should help you

declare @mytime datetime = '20160917 07:56'

Select ..
where @mytime between DATEADD(MINUTE, DATEDIFF(MINUTE, 0, tstart ), 0) and 
                                          DATEADD(MINUTE, DATEDIFF(MINUTE, 0, tEnd ), 0)

Upvotes: 0

Related Questions