Different Date format in predicate when comparing against a date or datetime

Question

I have the following query:

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   MyDateTimeCol >= '2019-11-25' AND MyDateTimeCol < '2019-11-26'

Which gives me the error

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

If I change the WHERE clause to

WHERE   MyDateTimeCol >= '2019-25-11' AND MyDateTimeCol < '2019-26-11'

This works.

However, I change my original query to the equivalent (performance issues aside)

SELECT  CAST(MyDateTimeCol AS DATE) AS MyDate,
        Col1,
        Col2
FROM    MyTable
WHERE   CAST(MyDateTimeCol AS DATE) = '2019-11-25'

The query works without any errors

Why does casting the datetime as a date mean I have to use a different Datetime format in the predicate?

Answer 1

In your first example, you compare a datetime to a string. The format for that string is YYYY-MM-DD, meaning that the login's language will determine what is to be interpreted and month vs day. You seems to be in the UK, I assume a British login. Check out for instance sp_helplanguage you'll see how the "separated" datetime format is interpreted for various languages.

The "separated" format is, btw, not to be confused with the ISO 8601 format, which is YYYY-MM-DDTHH:MM:SS (the letter T is what makes the difference).

When you cast the column to the date type, you no longer compare the string to datetime, you compare it to the more modern date type. This will always interpret the "separated" format as ymd, and is in other words not dependent on the login's language.

If you were using the more modern datetime2, btw, you wouldn't have the problem in the first place, since it is handled as date.

See my datetime article for further elaboration: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes .