Convert a dattime to a date in where clause gives incorrect results

_{Friday, October 11, 2013 4:33 AM}

Hi,

I have a simple select query to filter data based on dates.

select *
FROM tableA FDL

WHERE CONVERT(CHAR(10), DEPARTURE_DATE, 103) > '08/27/2013'

Thsi query gives me records with departure dates in 2008 and 2009 also which is wrong. It works fine if I wrote it as below:

select *
FROM tableA FDL

WHERE  DEPARTURE_DATE > '08/27/2013'

Can anyone explain why SQL server behaves this way?

Thanks,

Preetha

_{Friday, October 11, 2013 4:45 AM ✅Answered}

Can anyone explain why SQL server behaves this way?

The first query converts the (presumably) datetime value to a string so character comparison rules (left to right) are used instead of the datetime comparison rules (chronological order) you want.

I'm not sure why you are converting the datetime to a string but my guess is that you want to evaluate only the date portion of a datetime type.  If that is your situation for a date range query, I suggest an inclusive start date and exclusive end date and specify date literals in ISO 8601 format (or use parameters).  This will allow indexes on the date column to be used efficiently.  For example, to select rows for a given day:

SELECT * 
FROM dbo.tableA FDL
WHERE  DEPARTURE_DATE >= '20130828'
AND DEPARTURE_DATE < '20130829';

Dan Guzman, SQL Server MVP, http://www.dbdelta.com

_{Friday, October 11, 2013 4:38 AM}

because in the first case because of convertion it does comparison based on text sequence (as string value) and not as dates. In second case it interprets both values (DEPARTURE_DATE field value as well as '08/27/2013') as date values and hence comparison happens in date sequence itself giving required result.

Keep in mind that in SQL Server dates are stored internally as numbers with 0 corresponding to base date of 1 Jan 1900

_{Friday, October 11, 2013 4:39 AM}

What is  DEPARTURE_DATE column datatype? Why is there any need to CAST it to the CHAR? In order to explain the issue , please provide sample data.

BTW what it you changed the date format in your WHERE condition to > '27/08/2013', what does it return?

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

_{Friday, October 11, 2013 4:45 AM}

Thanks for the explanation. I did not realize that I am comparing the strings. Now I get the point. But one more doubt, in the second case also I am comparing the datetime values with a string. How does SQL server interprets the hard coded value as date?

_{Friday, October 11, 2013 4:49 AM}

This is basic. You are converting the date to a character of size 10. Then you are comparing the character value with another charcater value '08/27/2013'.

Character comparison is different from date or numeric comparison. Collations, character encoding yake in to play. A better approach would be to compare similar typed objects to avoid unexpected errors like the below:

select * 
FROM tableA FDL
WHERE CAST(CONVERT(CHAR(10), DEPARTURE_DATE, 103) AS DATETIME) > '08/27/2013'

Here the literal value implicitly converted to datetime type.

NB: this is an example please note that the query is not SARGable.

Krishnakumar S

_{Friday, October 11, 2013 5:00 AM}

It undergoes implicit conversion in second case to convert the string value '08/27/2013' to date equivalent as field to be compared is of datetime datatype

Here's an article I wrote on explaining various ways in which date range comparison can be implemented

http://visakhm.blogspot.in/2012/12/different-ways-to-implement-date-range.html

_{Friday, October 11, 2013 5:15 AM}

Don't use MM/DD/YYYY date literals. Use ANSI YYYY-MM-DD.

Date range predicates:

http://www.sqlusa.com/bestpractices2008/between-dates/

Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012