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
  • 23,615
  • 19
  • 137
  • 163
LocustHorde
  • 6,065
  • 16
  • 58
  • 89
  • ``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
  • 408,740
  • 77
  • 567
  • 659
  • 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
  • 40,660
  • 13
  • 93
  • 109