將常值日期字串轉換成 DATE 值的非決定性轉換Nondeterministic conversion of literal date strings into DATE values

允許將您的 CHARACTER 字串轉換成 DATE 資料類型時請務必小心。Use caution when allowing conversion of your CHARACTER strings into DATE data types. 原因是這類轉換通常都是「非決定性」 。The reason is that such conversions are often nondeterministic.

您可以透過說明 SET LANGUAGESET DATEFORMAT 的設定來控制這些非決定性轉換。You control these nondeterministic conversions by accounting for the settings of SET LANGUAGE and SET DATEFORMAT.

SET LANGUAGE 範例:波蘭文的月份名稱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?

例如,假設一個單字 listopad,此為月份的名稱。For example, consider the word listopad, which is the name of a month. 但該月份會根據 SQL 系統相信其正在使用的語言而有所不同:But which month it is depends on the language the SQL system believes is being used:

  • 若是波蘭文,則 listopad 可翻譯成 11 月 (即英文中的 November)。If Polish, then listopad translates to month 11 (November in English).
  • 若是克羅埃西亞文,則 listopad 可翻譯成 10 月 (即英文中的 October)。If Croatian, then listopad translates to month 10 (October in English).

SET LANGUAGE 的程式碼範例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 範例SET DATEFORMAT example

  • SET DATEFORMAT dmy;

上述 dmy 格式表示範例日期字串 '01-03-2018' 會解譯為表示「2018 年 3 月的第一天」 。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.

若改為指定 mdy,則相同的 '01-03-2018' 字串就會表示「2018 年 1 月的第三天」 。If instead mdy was specified, then the same '01-03-2018' string would mean the third day of January in 2018.

若指定 ymd,則無法保證輸出結果為何。And if ymd was specified, there is no guarantee of what the output would be. '2018' 的數值對天來說太大了。The numeric value of '2018' is too large to be a day.

特定國家/地區Specific countries

在日本和中國,會使用 ymd 的 DATEFORMAT。In Japan and China, the DATEFORMAT of ymd is used. 格式部分為由大單位至小單位的合理順序。The format's parts are in a sensible sequence of largest unit to smallest. 因此,此格式的排序狀況相當良好。Consequently, this format sorts well. 此格式被視為是「國際」 格式。This format is considered to be the international format. 其為國際格式的原因是四位數年份並不明確,且目前在地球上沒有任何國家/地區使用古老的 ydm 格式。It is international because the four digits of the year are unambiguous, and no country on Earth uses the archaic format of ydm.

在其他國家/地區 (例如德國和法國) 中,DATEFORMAT 為 dmy,其表示 'dd-mm-yyyy'In other countries such as Germany and France, the DATEFORMAT is dmy, meaning 'dd-mm-yyyy'. dmy 格式的排序狀況並不良好,但仍然是由小單位至大單位的合理順序。The dmy format does not sort well, but it is a sensible sequence of smallest unit to largest.

美國和密克羅尼西亞聯邦是使用 mdy 的唯二國家/地區,此格式無法排序。The United States, and the Federated States of Micronesia, are the only countries that use mdy, which does not sort. 格式的混合順序符合語音上說明日期的模式。The format's mixed sequence matches a pattern of verbal speech in spoken dates.

SET DATEFORMAT 的程式碼範例:mdydmyCode example of SET DATEFORMAT: mdy versus dmy

下列 Transact-SQL 程式碼範例會使用相同日期字元字串,搭配三種不同的 DATEFORMAT 設定。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
***/

在上述程式碼範例中,最後一個範例的格式 ymd 與輸入字串不符。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 無法保證這類不相符所產生的輸出值。Microsoft does not guarantee the output value from such mismatches.

CONVERT 可提供「決定性」 日期格式控制的明確程式碼CONVERT offers explicit codes for deterministic control of date formats

我們的 CAST 和 CONVERT 文件文章會列出您可以與 CONVERT 函式「決定性地」 控制日期轉換搭配使用的明確程式碼。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.

相容性層級 90 及以上Compatibility level 90 and above

在 SQL Server 2000 中,相容性層級為 80。In SQL Server 2000, the compatibility level was 80. 針對層級設定 80 或以下,隱含日期轉換具決定性。For level settings of 80 or below, implicit date conversions were deterministic.

從 SQL Server 2005 和其相容性層級 90 開始,隱含日期轉換不具決定性。Starting with SQL Server 2005 and its compatibility level of 90, implicit date conversions became nondeterministic. 從層級 90 開始,日期轉換依存於 SET LANGUAGE 和 SET DATEFORMAT。Date conversions became dependent on SET LANGUAGE and SET DATEFORMAT starting with level 90.

UnicodeUnicode

非 Unicode 字元資料與定序之間的轉換也被視為非決定性。Conversion of non-Unicode character data between collations is also considered nondeterministic.

另請參閱See also