11

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.

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
LocustHorde
  • 6,361
  • 16
  • 65
  • 94
  • ``date_created`` is a DATETIME field, so I was trying to delete the time bit by converting it to int? someone told me that's how its done, sorry I might be confused! – LocustHorde Jun 09 '11 at 10:50
  • possible duplicate of [datetime in where clause](http://stackoverflow.com/questions/1947436/datetime-in-where-clause) – BuZZ-dEE Dec 10 '14 at 13:16

2 Answers2

26

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'
gbn
  • 422,506
  • 82
  • 585
  • 676
  • okay that was easy. thanks. Also, my date_created is DATETIME. should I be worried about that? – LocustHorde Jun 09 '11 at 10:46
  • sorry, one more question, does between clause include the boundary dates? – LocustHorde Jun 09 '11 at 10:47
  • @LocustHorde: 1. between is inclusive 2. do you have times in your datetime values? – gbn Jun 09 '11 at 10:48
  • Hi, so how do I deal with datetime? I used int convert so i can chuck the time bit? – LocustHorde Jun 09 '11 at 10:48
  • @LocustHorde: You can use this `select cast(floor(cast(date_created as float)) as datetime)` to remove the time portion if needed. – codingbadger Jun 09 '11 at 10:52
  • 1
    @LocustHorde, @Barry: changing the condition removes the need to have a function on date_created which will invalidate any index usage. Also see http://stackoverflow.com/q/133081/27535 anyway – gbn Jun 09 '11 at 11:06
2

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'

codingbadger
  • 42,678
  • 13
  • 95
  • 110