When I run the following statement:
SELECT CAST ('12/08/1988' AS DATE) AS BIRTHDAY
I get the 1988-12-08, not 1988-08-12 in ISO 8601 format. How does SQL Server decide which format to apply for parsing? I know the best practices, I'm just interested in where does it take it and how it can be changed?
First.. Format for parsing dates is based on an environment language currently set in the active session. You can see the current language with SELECT @@LANGUAGE and you can change it using SET LANGUAGE command.
Next you can override the format of current language by setting your own date format with SET DATEFORMAT. However, mind that if you change language again, it overrides the format settings.
Here are few examples on how different settings behave and affect your CAST query:
SET LANGUAGE Italian
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName;
SET LANGUAGE English
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName;
SET DATEFORMAT DMY
SELECT @@LANGUAGE
, CAST ('12/08/1988' AS DATE) AS BIRTHDAY
, DATENAME(month,CAST ('12/08/1988' AS DATE)) AS MonthName;
Default language setting for each new query is set on login level. You can change it by finding in Object Explorer on server->Logins->YourLogin->Properties->Default Language or with ALTER LOGIN command
Further, there is also a default language on server, which affects default choice for newly created logins.
More about that you can find in this question: How to change default language for SQL Server?
At the end, like others said, you should avoid confusion by using CONVERT with style, ISO format and appropriate data types.
My tips: If you want to convert string to date in adhoc queries (like in example), always use ISO format and there is no need to worry about format and not even a need to convert:
SELECT * FROM Table WHERE DateColumn = '20170325'
If you want to convert date to string (for display) use CONVERT with desired style:
SELECT CONVERT(NVARCHAR(30), DateColumn, 104) FROM Table
SQL Server doesn't have a "culture", it will just always parse a value in that format as mm/dd/yyyy (i.e. US format). you can control it to some degree using DATEFORMAT, for example:
SET DATEFORMAT mdy
However, these are fairly basic formats and you really should avoid relying on them.
If you want to change it, you should use the CONVERT function, or the newer PARSE. For example, to specify British format:
SELECT CONVERT(DATE, '12/08/1988', 103)
Or:
SELECT PARSE('12/08/1988' AS DATETIME USING 'en-GB')
Doing this will always be more reliable, regardless of date formats:
SELECT CAST ('1988-08-12' AS DATE) AS BIRTHDAY
As you're not specifying the format, it will use default date format on your SQL instance, which looks like English US.
