Search code examples
sql-serversql-server-2017isodate

Default Date Formatting in SQL Server 2017

I am currently debugging a scenario in which dates need to be read from a string in a stored procedure.

The code that we have used is a simple convert statement. e.g.

SELECT CONVERT(DATETIME, @dateInput)

The problem comes when I need to test the dates coming from the Chinese date format (yyyy.mm.dd) which is ISO 102.

What settings in Windows 10 / SQL Server must be set so that running SELECT CONVERT(DATETIME, '2020.05.16') will not return "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I understand that using SELECT CONVERT(DATETIME, '2020.05.16', 102) would work but that would make the universal stored procedure incorrect when running with different windows date formats

Solution

  • Along with changing my windows settings to reflect the same date format I also changed:

    • My default language of my SQLEXPRESS server in Properties > Advanced > Default Language

    • My default language of my SQL User Login Properties > General > Default Language

    This meant that it could read the direct string value without needed the ISO 102 identifier.