Nondeterministic conversion of literal date strings into DATE values

Artikkel
11/18/2022

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Use caution when allowing conversion of your CHARACTER strings into DATE data types. The reason is that such conversions are often nondeterministic.

You control these nondeterministic conversions by accounting for the settings of SET LANGUAGE and SET DATEFORMAT.

SET LANGUAGE example: Month name in Polish

SET LANGUAGE Polish;

A character string can be the name of a month. But is the name in English, or Polish, or Croatian, or in another language? And, will the user's session be set to the correct corresponding LANGUAGE?

For example, consider the word listopad, which is the name of a month. But which month it is depends on the language the SQL system believes is being used:

If Polish, then listopad translates to month 11 (November in English).
If Croatian, then listopad translates to month 10 (October in English).

Code example of SET LANGUAGE

--SELECT alias FROM sys.syslanguages ORDER BY alias;

DECLARE @yourInputDate  NVARCHAR(32) = '28 listopad 2018';

SET LANGUAGE Polish;
SELECT CONVERT(DATE, @yourInputDate) AS [SL_Polish];

SET LANGUAGE Croatian;
SELECT CONVERT(DATE, @yourInputDate) AS [SL_Croatian];

SET LANGUAGE English;


/***  Actual output:  For the two months, note the 11 versus the 10.
SL_Polish
2018-11-28

SL_Croatian
2018-10-28
***/

SET DATEFORMAT example

SET DATEFORMAT dmy;

The preceding dmy format says that an example date string of '01-03-2018' would be interpreted to mean the first day of March in the year 2018.

If instead mdy was specified, then the same '01-03-2018' string would mean the third day of January in 2018.

And if ymd was specified, there's no guarantee of what the output would be. The numeric value of '2018' is too large to be a day.

Specific countries/regions

In Japan and China, the DATEFORMAT of ymd is used. The format's parts are in a sensible sequence of largest unit to smallest. So, this format sorts well. This format is considered to be the international format. It's international because the four digits of the year are unambiguous, and no country/region on Earth uses the archaic format of ydm.

In other countries/regions such as Germany and France, the DATEFORMAT is dmy, meaning 'dd-mm-yyyy'. The dmy format doesn't sort well, but it's a sensible sequence of smallest unit to largest.

The United States, and the Federated States of Micronesia, are the only countries/regions that use mdy, which doesn't sort. The format's mixed sequence matches a pattern of verbal speech in spoken dates.

Code example of SET DATEFORMAT: mdy versus dmy

The following Transact-SQL code example uses the same date character string with three different DATEFORMAT settings. A run of the code produces the output shown in the comment:

DECLARE @yourDateString NVARCHAR(10) = '12-09-2018';
PRINT @yourDateString + '  = the input.';

SET DATEFORMAT dmy;
SELECT CONVERT(DATE, @yourDateString) AS [DMY-Interpretation-of-input-format];

SET DATEFORMAT mdy;
SELECT CONVERT(DATE, @yourDateString) AS [MDY-Interpretation-of-input-format];

SET DATEFORMAT ymd;
SELECT CONVERT(DATE, @yourDateString) AS [YMD-Interpretation--?--NotGuaranteed];


/***  Actual output:
12-09-2018  = the input.

DMY-Interpretation-of-input-format
2018-09-12

MDY-Interpretation-of-input-format
2018-12-09

YMD-Interpretation--?--NotGuaranteed
2018-12-09
***/

In the preceding code example, the final example has a mismatch between format ymd versus the input string. The third node of the input string represents a numeric value that is too large to be a day. Microsoft doesn't guarantee the output value from such mismatches.

CONVERT offers explicit codes for deterministic control of date formats

Our CAST and CONVERT documentation article lists explicit codes that can you can use with the CONVERT function to deterministically control date conversions. Every month the article has one of our highest pageview counts.

Compatibility level 90 and above

In SQL Server 2000, the compatibility level was 80. For level settings of 80 or below, implicit date conversions were deterministic.

Starting with SQL Server 2005 and its compatibility level of 90, implicit date conversions became nondeterministic. Date conversions became dependent on SET LANGUAGE and SET DATEFORMAT starting with level 90.

Unicode

Conversion of non-Unicode character data between collations is also considered nondeterministic.

Del via