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

回答1:

You can use BETWEEN:

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

回答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)

回答3:

You could try with this:

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