CODEWITHSUNDEEP
sql-servert-sqldate
rumi
rumi

Reputation: 3298

find a date between two dates in T SQL

I have two DateTime columns in my table, ArrivalDateTime,DepartureDateTime with values like '26/11/2012 00:00:00' '28/11/2012 00:00:00' Now I want to find all the records from this table where a given date say 27/11/2012 exist between those dates using T Sql

Upvotes: 4

Views: 15580

Answers (3)

Devraj Gadhavi
Devraj Gadhavi

Reputation: 3611

Try this.

DECLARE @GivenDate VARCHAR(10)
SET @GivenDate = '27/11/2012'

SELECT * FROM myTable WHERE CONVERT(DATETIME, ArrivalDateTime, 103) < CONVERT(DATETIME, @GivenDate, 103) AND CONVERT(DATETIME, DepartureDateTime, 103) > CONVERT(DATETIME, @GivenDate, 103)

If you want to include the arrival & departure dates.

SELECT * FROM myTable WHERE CONVERT(DATETIME, ArrivalDateTime, 103) <= CONVERT(DATETIME, @GivenDate, 103) AND CONVERT(DATETIME, DepartureDateTime, 103) >= CONVERT(DATETIME, @GivenDate, 103)

If you want to compare only dates & not time then try this.

SELECT * FROM myTable WHERE CONVERT(DATE, ArrivalDateTime, 103) <= CONVERT(DATE, @GivenDate, 103) AND CONVERT(DATE, DepartureDateTime, 103) >= CONVERT(DATE, @GivenDate, 103)

Upvotes: 1

Nikola Davidovic
Nikola Davidovic

Reputation: 8666

You could try with this:

select * from MYTABLE where ArrivalDateTime < '2012-11-27' and DepartureDateTime > '2012-11-27'

Upvotes: 0

Chris Van Opstal
Chris Van Opstal

Reputation: 37587

You can use BETWEEN:

SELECT * FROM table 
WHERE '2012-11-27' BETWEEN ArrivalDateTime AND DepartureDateTime

Upvotes: 5

Related Questions