CODEWITHSUNDEEP
t-sqldate
LocustHorde
LocustHorde

Reputation: 6399

Select rows within a date range in T-SQL

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.

Upvotes: 11

Views: 36338

Answers (2)

gbn
gbn

Reputation: 432261

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'

Upvotes: 26

codingbadger
codingbadger

Reputation: 43984

Either don't convert the date_created to an int or use integers for your data values

I would leave the date_created as a date.

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

Upvotes: 2

Related Questions