リテラル日付文字列を DATE 値に非決定論的に変換する

[アーティクル]
05/30/2023

適用対象:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

CHARACTER 文字列を DATE データ型に変換することを許可するときは注意が必要です。このような変換はしばしば非決定論的であるためです。

このような非決定論的な変換は SET LANGUAGE と SET DATEFORMAT の設定を考慮することで制御します。

SET LANGUAGE 例: ポーランド語の月名

SET LANGUAGE Polish;

文字の文字列には月の名前を指定できます。しかしながら、名前は英語でしょうか。ポーランド語でしょうか。クロアチア語でしょうか。あるいは別の言語でしょうか。また、ユーザーのセッションは該当する LANGUAGE に設定されるでしょうか。

たとえば、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 月の 3 番目の日として解釈されます。

また、ymd が指定された場合、何が出力されるかはわかりません。 "2018" という数値は日としては大きすぎる数字です。

特定の国/地域

日本と中国では、DATEFORMAT に ymd が使用されます。この形式の各部分は、最も大きいユニットから最も小さいユニットへという理にかなった順序になっています。したがって、このフォーマットは効果的に並べ替えられます。この形式は国際式と見なされています。国際式と見なされるのは、4 桁は年度であることがはっきりしており、ydm という古式を使用している国/地域はないからです。

ドイツやフランスなど、その他の国や地域では、DATEFORMAT は dmy です。これは "dd-mm-yyyy" を意味します。 dmy 書式は並べ替えが効果的ではありませんが、最も小さいユニットから最も大きいユニットへとなっており、順序としては理にかなっています。

mdy は米国とミクロネシア連邦だけで使用されています。これは並べ替えられません。この書式の混在した順序は、日付を読み上げるときの発話パターンに一致するものです。

SET DATEFORMAT のコード例: mdy と dmy の違い

次の Transact-SQL コード例では同じ日付文字の文字列が使用されていますが、3 つの異なる 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 形式と入力文字列が一致していません。入力文字列の 3 番目のノードは数値ですが、日としては大きすぎます。 Microsoft では、このような不一致から値が出力されることは保証しません。

CONVERT からは、日付書式を決定的に制御するための明示的コードが与えられます。

CAST と CONVERT に関する Microsoft のドキュメント記事には、CONVERT 関数と併用し、日付変換を決定的に制御できる明示的コードの一覧があります。この記事は毎月、飛び抜けて高い閲覧数を記録します。

互換性レベル 90 以上

SQL Server 2000 では、互換性レベルは 80 でした。 80 以下のレベル設定の場合、暗黙的日付変換は決定的でした。

互換性レベルが 90 の SQL Server 2005 からは、暗黙的日付変換が非決定論的になりました。レベル 90 より、日付変換は SET LANGUAGE と SET DATEFORMAT に依存するようになりました。

Unicode

照合順序間で行われる Unicode 以外の文字データの変換も非決定論的であると見なされます。