CODEWITHSUNDEEP
sqlsql-server
Narendra Pal
Narendra Pal

Reputation: 6604

How to use between clause for getting data between two dates?

I have date field in the database in the format 2012-03-17 19:50:08.023.

I want to create a select query which gives me the data collected in the March month. But I am not able to achieve this.

I am trying following query.

select * from OrderHeader where
Convert(varchar,UploadDt,103) between '01/03/2013' and '31/03/2013' 
and DistUserUniqueID like  '6361%'

This query gives me data for all the dates.

select * from OrderHeader where
UploadDt between '01/03/2013' and '31/03/2013' and DistUserUniqueID like  '6361%'

This query gives me the error as Msg 242, Level 16, State 3, Line 1 The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Please help me resolve this.

Thanks in advance

Upvotes: 2

Views: 32854

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

The first query returns all dates because you are converting your column to a string. Not sure why you are doing this. So when you say BETWEEN '01/anything' AND '31/anything', when you consider it is now just a string, that is going to match all "dates" in the column, regardless of month and year, since your WHERE clause will cover every single day possible (well, with the exception of the 31st of months other than March, and the first day in January and February - so not all the data but a very large percentage). '15/11/2026', for example, is BETWEEN '01\03\2013' AND '31/03/2013'.

Think about that for a second. You have datetime data in your database, and you want to convert it to a string before you query it. Would you also convert salary to a string before comparing it? If so, the person making $70,000 will look like they are making more than the person making $690,000, since character-based sorting starts at the first character and doesn't consider length.

The second query fails because you are using a regional, ambiguous format for your dates. You may like dd/mm/yyyy but clearly your server is based on US English formatting where mm/dd/yyyy is expected.

The solution is to use a proper, unambiguous format, such as YYYYMMDD.

BETWEEN '20130301' AND '20130313'

However you shouldn't use BETWEEN - since this is a DATETIME column you should be using:

WHERE UploadDt >= '20130301'
  AND UploadDt <  '20130401'

(Otherwise you will miss any data from 2013-03-31 00:00:00.001 through 2013-03-31 23:59:59.997.)

If you really like BETWEEN then on 2008+ (you didn't tell us your version) you can use:

WHERE CONVERT(DATE, UploadDt) BETWEEN '20130301' AND '20130331'

More date-related tips here:

Finally, when converting to VARCHAR (or any variable-length data types), always specify a length.

Upvotes: 7

Kangkan
Kangkan

Reputation: 15571

Rewrite the query as

select * from OrderHeader where
UploadDt between '01/03/2013' and '01/04/2013' 
and DistUserUniqueID like  '6361%'

or 

select * from OrderHeader where
UploadDt between Convert(datetime,'01/03/2013', 103) and Convert(datetime,'01/04/2013',103) 
and DistUserUniqueID like  '6361%'

Upvotes: -1

Related Questions