將常值日期字串轉換成 DATE 值的非決定性轉換

  • 發行項

適用於：SQL ServerAzure SQL DatabaseAzure SQL 受控執行個體Azure Synapse AnalyticsAnalytics Platform System (PDW)

允許將您的 CHARACTER 字串轉換成 DATE 資料類型時請務必小心。 原因是這類轉換通常都是「非決定性」。

您可以透過說明 SET LANGUAGE 和 SET DATEFORMAT 的設定來控制這些非決定性轉換。

SET LANGUAGE 範例：波蘭文的月份名稱

  • SET LANGUAGE Polish;

字元字串可以是月份的名稱。 但名稱是英文、波蘭文、克羅埃西亞文或其他語言？ 此外，使用者的工作階段是否會設為正確對應語言？

例如，假設一個單字 listopad，此為月份的名稱。 但該月份會根據 SQL 系統相信其正在使用的語言而有所不同：

  • 若是波蘭文，則 listopad 可翻譯成 11 月 (即英文中的 November)。
  • 若是克羅埃西亞文，則 listopad 可翻譯成 10 月 (即英文中的 October)。

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 dmy;

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

若改為指定 mdy，則相同的 '01-03-2018' 字串就會表示「2018 年 1 月的第三天」。

若指定 ymd，則無法保證輸出結果為何。 '2018' 的數值對天來說太大了。

特定國家/區域

在日本和中國，會使用 ymd 的 DATEFORMAT。 格式部分為由大單位至小單位的合理順序。 因此，此格式的排序狀況良好。 此格式被視為是「國際」格式。 其為國際格式的原因是四位數年份並不明確，且目前在地球上沒有任何國家/區域使用古老的 ydm 格式。

在其他國家/區域 (例如德國和法國) 中，DATEFORMAT 為 dmy，表示 'dd-mm-yyyy'。 dmy 格式的排序狀況不良，但仍然是由小單位到大單位的合理順序。

美國和密克羅尼西亞聯邦是使用 mdy 的唯二國家/區域，此格式無法排序。 格式的混合順序符合語音上說明日期的模式。

SET DATEFORMAT 的程式碼範例：mdy 與 dmy

下列 Transact-SQL 程式碼範例會使用相同日期字元字串，搭配三種不同的 DATEFORMAT 設定。 執行程式碼會產生如註解中的輸出：

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 與輸入字串不符。 輸入字串之第三個節點表示對天來說太大的數值。 Microsoft 不保證這類不相符所產生的輸出值。

CONVERT 可提供「決定性」日期格式控制的明確程式碼

我們的 CAST 和 CONVERT 文件文章會列出您可以與 CONVERT 函式「決定性地」控制日期轉換搭配使用的明確程式碼。 每個月該文章都擁有我們最高的頁面瀏覽次數。

相容性層級 90 及以上

在 SQL Server 2000 中，相容性層級為 80。 針對層級設定 80 或以下，隱含日期轉換具決定性。

從 SQL Server 2005 和其相容性層級 90 開始，隱含日期轉換不具決定性。 從層級 90 開始，日期轉換依存於 SET LANGUAGE 和 SET DATEFORMAT。

Unicode

非 Unicode 字元資料與定序之間的轉換也被視為非決定性。

另請參閱