SQL query to select dates between two dates

you should put those two dates between single quotes like..

select Date, TotalAllowance from Calculation where EmployeeId = 1             and Date between '2011/02/25' and '2011/02/27'

or can use

select Date, TotalAllowance from Calculation where EmployeeId = 1             and Date >= '2011/02/25' and Date <= '2011/02/27'

keep in mind that the first date is inclusive, but the second is exclusive, as it effectively is '2011/02/27 00:00:00'

Since a datetime without a specified time segment will have a value of date 00:00:00.000, if you want to be sure you get all the dates in your range, you must either supply the time for your ending date or increase your ending date and use <.

select Date,TotalAllowance from Calculation where EmployeeId=1 and Date between '2011/02/25' and '2011/02/27 23:59:59.999'

OR

select Date,TotalAllowance from Calculation where EmployeeId=1 and Date >= '2011/02/25' and Date < '2011/02/28'

OR

select Date,TotalAllowance from Calculation where EmployeeId=1 and Date >= '2011/02/25' and Date <= '2011/02/27 23:59:59.999'

DO NOT use the following, as it could return some records from 2011/02/28 if their times are 00:00:00.000.

select Date,TotalAllowance from Calculation where EmployeeId=1 and Date between '2011/02/25' and '2011/02/28'

Try this:

select Date,TotalAllowance from Calculation where EmployeeId=1             and [Date] between '2011/02/25' and '2011/02/27'

The date values need to be typed as strings.

To ensure future-proofing your query for SQL Server 2008 and higher, Date should be escaped because it's a reserved word in later versions.

Bear in mind that the dates without times take midnight as their defaults, so you may not have the correct value there.