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.

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
– LocustHorde, Commented Jun 9, 2011 at 10:50

gbn · Accepted Answer · 2011-06-09 10:46:24Z

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'

edited Jun 9, 2011 at 10:46

answered Jun 9, 2011 at 10:45

gbn

434k84 gold badges602 silver badges689 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

LocustHorde Over a year ago

okay that was easy. thanks. Also, my date_created is DATETIME. should I be worried about that?

LocustHorde Over a year ago

sorry, one more question, does between clause include the boundary dates?

gbn Over a year ago

@LocustHorde: 1. between is inclusive 2. do you have times in your datetime values?

LocustHorde Over a year ago

Hi, so how do I deal with datetime? I used int convert so i can chuck the time bit?

gbn Over a year ago

@LocustHorde, @Barry: changing the condition removes the need to have a function on date_created which will invalidate any index usage. Also see stackoverflow.com/q/133081/27535 anyway

|

codingbadger · Accepted Answer · 2011-06-09 10:44:25Z

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'

answered Jun 9, 2011 at 10:44

codingbadger

44.3k13 gold badges99 silver badges113 bronze badges

1 Comment

ninesided Over a year ago

whilst I agree with the sentiment, this isn't really an answer to the question

Collectives™ on Stack Overflow

Select rows within a date range in T-SQL

2 Answers 2

6 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Linked

Related