1

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?

EngineerSpock
  • 2,575
  • 4
  • 35
  • 57
  • SQL Server doesn't have a "culture", it will just always parse a value in that format as mm/dd/yyyy – DavidG Aug 04 '17 at 14:03
  • So, SQL Server just has some predefined sense (so to speak) of input formats, right? – EngineerSpock Aug 04 '17 at 14:03
  • 2
    @DavidG, that's not entirely correct. You can set the dateformat using `SET DATEFORMAT ydm` to use a format like `1990/31/12` (as an example). I don't know if you can set the separators as well. – HoneyBadger Aug 04 '17 at 14:05
  • @HoneyBadger oh, thanks for the clarification. But nevertheless, is it true that SQL Server defines some default formats? And SET DATEFORMAT works per session? – EngineerSpock Aug 04 '17 at 14:08
  • @HoneyBadger Hmm yes, I'll add that to my answer – DavidG Aug 04 '17 at 14:08
  • @EngineerSpock, both statements are correct. `SET DATEFORMAT` is connection dependent. You can also set the default during installation. – HoneyBadger Aug 04 '17 at 14:09
  • 1
    There are only 2 formats that SQL Server will correctly parse, no matter the collation... YYYY-MM-DD & YYYYMMDD... Every other format will have some dependancy of the collation being used. – Jason A. Long Aug 04 '17 at 14:12
  • 1
    While `set dateformat` is limited to the current connection, you can create a login to always use a specific language setting: `ALTER LOGIN your_login WITH DEFAULT_LANGUAGE=British`. You can even add your own language by using `exec sp_addlanguage`. – HoneyBadger Aug 04 '17 at 14:16

3 Answers3

5

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
Nenad Zivkovic
  • 18,221
  • 6
  • 42
  • 55
4

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')
DavidG
  • 113,891
  • 12
  • 217
  • 223
  • 1
    this is not really correct, each login has a default language which is used to format dates, you can always choose language different than English to be default. – Nenad Zivkovic Aug 04 '17 at 14:26
0

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.

Tanner
  • 22,205
  • 9
  • 65
  • 83