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?
The ISO 8601 format does not depend on any of your local settings and is an international standard
.