CODEWITHSUNDEEP
sqlsql-serverdatetime
good-to-know
good-to-know

Reputation: 742

Get rows in between date time values

I have a table like below

Id    DateTimeIn                DateTimeOut
1     2016-12-03 09:05:22.123   2016-12-03 09:05:22.123
2     2016-12-03 10:00:00.415   2016-12-03 10:40:00.415
3     2016-12-03 11:04:52.253   2016-12-03 11:35:22.123

I would like to bring the values that comes in between the time ranges.

declare @DateTimeIn date = '2016-12-03 11:00:52.213'
declare @DateTimeOut date = '2016-12-03 11:45:52.213'

The row with Id 3 that comes in the input range. So I write the below query to bring that row as an output.

SELECT * 
FROM TimesheetEntries
WHERE @DateTimeIn BETWEEN DateTimeIn AND DateTimeOut 
   OR @DateTimeOut BETWEEN DateTimeIn AND DateTimeOut

But it bring no result. I dont know what is wrong in my query. Please help me!

Upvotes: 0

Views: 48

Answers (2)

Mansoor
Mansoor

Reputation: 4192

DECLARE @DateTimeIn datetime = '2016-12-03 11:00:52.213'
DECLARE @DateTimeOut datetime = '2016-12-03 11:45:52.213'

SELECT * FROM TimesheetEntries
WHERE 
(  
  DATEDIFF(DAY,DateTimeIn,@DateTimeIn) <= 0 AND
  DATEDIFF(DAY,DateTimeIn,@DateTimeOut) >= 0
) OR
(  
  DATEDIFF(DAY,DateTimeOut,@DateTimeIn) <= 0 AND
  DATEDIFF(DAY,DateTimeOut,@DateTimeOut) >= 0
)

Upvotes: 0

Kumar_Vikas
Kumar_Vikas

Reputation: 845

Make sure your variable is of type datetime and The column you are selecting is of type datetime.

declare @DateTimeIn datetime = '2016-12-03 11:00:52.213'
declare @DateTimeOut datetime = '2016-12-03 11:45:52.213'

SELECT * FROM TimesheetEntries
WHERE @DateTimeIn BETWEEN DateTimeIn and DateTimeOut 
OR @DateTimeOut BETWEEN DateTimeIn and DateTimeOut

Upvotes: 1

Related Questions