find a date between two dates in T SQL
15,552
Solution 1
You can use BETWEEN
:
SELECT * FROM table
WHERE '2012-11-27' BETWEEN ArrivalDateTime AND DepartureDateTime
Solution 2
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)
Author by
rumi
Updated on August 21, 2022Comments
-
rumi almost 2 years
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
-
Nico over 11 yearsregard, that between also returns true, if date = ArrivalDateTime or date = DepartureDateTime