Search code examples
sql-serversql-server-2017

Literal datetimes as yyyy-mm-dd throw an exception

I have a database configured with Spanish settings, when I use literal datetimes as yyyy-mm-dd I get a varchar out of range conversion error.

select convert(datetime, '2014-11-19')

returns

Msg 242, Level 16, State 3, Line 3 La conversión del tipo de datos varchar en datetime produjo un valor fuera de intervalo.

I have to write those datetimes in spanish format. This works fine:

select convert(datetime, '2014-11-19')

Shouldn't I be able use both, the Spanish and the yyyy-mm-dd format ?. I don't want to use the Spanish format because a new costumer could use the same database with different regional settings.

I don't have any problem with date literals, only with datetimes. This works fine in both Spanish and yyyy-mm-dd format.

select convert(date, '2014-11-19'), 
       convert(date, '19-11-2014')

Thank you.

Solution

  • yyyy-MM-dd is not unambiguous in SQL Server. Use either yyyyMMdd or yyyy-MM-ddThh:mm:ss.nnnnnnn. If you aren't American SQL Server (foolishly) reads yyyy-MM-dd as yyyy-dd-MM for the old data date and time data types. Based on your error, you aren't American and hence the error.

    Note: yyyy-MM-dd is unambiguous for the newer date and time data types. Microsoft fixed that "feature".

    Alternatively, you can provide a style code to CONVERT to specify the format. In this case, that would be style 23: CONVERT(date time,'2014-11-19',23)