Select rows within a date range in T-SQL

Question

I have a set of rows, each with a date value, and I need to select rows that fall within a specific date range. How can I do this?

select * from table where convert(int,date_created) between //what should go here?

I want to select between '20-10-2010' and '22-10-2010'.

It keeps complaining about string to date conversion.

Answer 1

You need to use yyyymmdd which is the safest format for SQL Server

select * from table
where date_created BETWEEN '20101020' and '20101022'

Not sure why you had CONVERT to int there...

Note: if date_created has a time component that this fails because it assume midnight.

Edit:

To filter for the day 20 Oct 2010 to 22 Oct 2010 inclusive, if the date_created column has times, without applying a function to date_created:

where date_created >= '20101020' and date_created < '20101023'