CONVERT 関数で日付の文字列変換時に指定できるスタイル (書式) SQL Server

CONVERT 関数で日付型のデータを文字列に変換する際にスタイル (書式) が設定できます。
普段の開発業務では、111 (yyyy/mm/dd), 112 (yyyymmdd) をよく使いますが、どれくらいの種類があるのか気になったので調べてみました。

指定できるスタイル

Microsoft の Transact-SQL の CAST および CONVERT のページによると、CONVERT 関数で日付を文字列に変換する際に指定できるスタイルは、以下の通りになっています。

2 桁の年
(yy) (1)
4 桁の年
(yyyy)
Standard 入力/出力 (3)
0 または 100 (1、2) datetime および smalldatetime の既定値 mon dd yyyy hh:miAM (または PM)
1 101 米国 1 = mm/dd/yy
101 = mm/dd/yyyy
2 102 ANSI 2 = yy.mm.dd
102 = yyyy.mm.dd
3 103 イギリス/フランス 3 = dd/mm/yy
103 = dd/mm/yyyy
4 104 German 4 = dd.mm.yy
104 = dd.mm.yyyy
5 105 Italian 5 = dd-mm-yy
105 = dd-mm-yyyy
6 106 (1) 6 = dd mon yy
106 = dd mon yyyy
7 107 (1) 7 = Mon dd, yy
107 = Mon dd, yyyy
8 または 24 108 hh:mi:ss
9 または 109 (1、2) 既定値 + ミリ秒 mon dd yyyy hh:mi:ss:mmmAM (または PM)
10 110 米国 10 = mm-dd-yy
110 = mm-dd-yyyy
11 111 日本 11 = yy/mm/dd
111 = yyyy/mm/dd
12 112 ISO 12 = yymmdd
112 = yyyymmdd
13 または 113 (1、2) ヨーロッパの既定値 + ミリ秒 dd mon yyyy hh:mi:ss:mmm (24h)
14 114 hh:mi:ss:mmm (24h)
20 または 120 (2) ODBC 標準 yyyy-mm-dd hh:mi:ss (24h)
21 または 25 または 121 (2) time、date、datetime2、および datetimeoffset の ODBC 標準 (ミリ秒を含む) の既定値 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 米国 mm/dd/yy hh:mi:ss AM (または PM)
23 ISO8601 yyyy-mm-dd
126 (4) ISO8601 yyyy-mm-ddThh:mi:ss.mmm (スペースなし)

注: ミリ秒 (mmm) の値が 0 の場合、ミリ秒を表す小数部の値は表示されません。 たとえば、値 “”2012-11-07T18:26:20.000″” は、””2012-11-07T18:26:20″”‘ と表示されます。

127(6、7) ISO 8601 (タイム ゾーン Z) yyyy-mm-ddThh:mi:ss.mmmZ (スペースなし)

注: ミリ秒 (mmm) の値が 0 の場合、ミリ秒を示す小数部の値は表示されません。 たとえば、値 “”2012-11-07T18:26:20.000″” は、””2012-11-07T18:26:20″” と表示されます。

130 (1、2) Hijri (5) dd mon yyyy hh:mi:ss:mmmAM

このスタイルでは、mon は月の正式名に関する複数トークンの Hijri (イスラム暦) ユニコード表現を表します。 この値は、SSMS の既定の米国用インストールでは正しく表示されません。

131 (2) Hijri (5) dd/mm/yyyy hh:mi:ss:mmmAM

1 これらのスタイル値で返される結果は非決定的です。 この場合は、すべての (yy) (2 桁の年) スタイルと、(yyyy) (4 桁の年) スタイルのサブセットが対象となります。

2 既定値 (0 または 100、9 または 109、13 または 113、20 または 120、23、および 21 または 25 または 121) は常に 4 桁の年 (yyyy) を返します。

3 datetime に変換する場合は入力になり、文字データに変換する場合は出力になります。

4 XML で使用するよう設計されています。 datetime または smalldatetime から文字データに変換する場合の出力形式は、前の表に示したとおりです。

5 Hijri とはいくつかのバリエーションがあるカレンダー システムです。 SQL Server ではクウェート アルゴリズムが使用されます。

重要
SQL Server の既定では、2 桁の年は、終了年 2049 年を基準に解釈されます。 つまり、SQL Server は 2 桁の年 49 を 2049 年と解釈し、2 桁の年 50 を 1950 年と解釈します。 クライアント アプリケーションの中には、Automation オブジェクトに基づくものなど、2030 年を終了年とするものも多くあります。 SQL Server には、SQL Server によって使用される基準年を変更するための 2 桁年基準構成オプションが用意されています。 これにより、日付の一貫性のある処理を行うことができます。 年は 4 桁で指定することをお勧めします。

6 文字データを datetime または smalldatetime 型にキャストする場合のみサポートされます。 日付部分または時刻部分のみを表す文字データを、datetime または smalldatetime データ型にキャストするとき、時刻部分が指定されていないと 00:00:00.000 に設定され、日付部分が指定されていないと 1900-01-01 に設定されます。

7 タイム ゾーン インジケーター Z は省略可能です。これを使用すると、タイム ゾーン情報が含まれる XML の datetime 値から、タイム ゾーン情報が含まれない SQL Server datetime 値へのマップが容易になります。 Z は、タイム ゾーン UTC-0 を示します。 + または – 方向の HH:MM オフセットは、他のタイム ゾーンを示します。 例: 2006-12-12T23:45:12-08:00」を参照してください。

smalldatetime を文字データに変換する場合、秒またはミリ秒を含むスタイルではそれらの位置に 0 が表示されます。 datetime または smalldatetime の値から変換するときは、適切な char または varchar データ型の長さを使用して、日付の不要な部分を切り捨てます。

時刻を含むスタイルを使って文字データから datetimeoffset に変換すると、タイム ゾーン オフセットが結果に追加されます。

出典:https://docs.microsoft.com/ja-jp/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15

2 桁の年 (yy) で取得できるのが 13 種類、4 桁の年 (yyyy) で取得できるのが 22 種類あります。

上記の一覧から、スタイル番号と変換後の書式のみ抜き出した表を以下に掲載しておきます。

No スタイル 変換後の書式
1 0 mon dd yyyy hh:miAM (または PM)
2 1 mm/dd/yy
3 2 yy.mm.dd
4 3 dd/mm/yy
5 4 dd.mm.yy
6 5 dd-mm-yy
7 6 dd mon yy
8 7 Mon dd, yy
9 8 hh:mi:ss
10 9 mon dd yyyy hh:mi:ss:mmmAM (または PM)
11 10 mm-dd-yy
12 11 yy/mm/dd
13 12 yymmdd
14 13 dd mon yyyy hh:mi:ss:mmm (24h)
15 14 hh:mi:ss:mmm (24h)
16 20 yyyy-mm-dd hh:mi:ss (24h)
17 21 yyyy-mm-dd hh:mi:ss.mmm (24h)
18 22 mm/dd/yy hh:mi:ss AM (または PM)
19 23 yyyy-mm-dd
20 24 hh:mi:ss
21 25 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 100 mon dd yyyy hh:miAM (または PM)
23 101 mm/dd/yyyy
24 102 yyyy.mm.dd
25 103 dd/mm/yyyy
26 104 dd.mm.yyyy
27 105 dd-mm-yyyy
28 106 dd mon yyyy
29 107 Mon dd, yyyy
30 108 hh:mi:ss
31 109 mon dd yyyy hh:mi:ss:mmmAM (または PM)
32 110 mm-dd-yyyy
33 111 yyyy/mm/dd
34 112 yyyymmdd
35 113 dd mon yyyy hh:mi:ss:mmm (24h)
36 114 hh:mi:ss:mmm (24h)
37 120 yyyy-mm-dd hh:mi:ss (24h)
38 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
39 126 yyyy-mm-ddThh:mi:ss.mmm (スペースなし)
40 127 yyyy-mm-ddThh:mi:ss.mmmZ (スペースなし)
41 130 dd mon yyyy hh:mi:ss:mmmAM
42 131 dd/mm/yyyy hh:mi:ss:mmmAM

SQL の実行

では、実際にそれぞれのスタイルを指定して SQL を実行してみます。

実行結果

No スタイル 変換値
1 0 02 3 2001 4:05AM
2 1 02/03/01
3 2 01.02.03
4 3 03/02/01
5 4 03.02.01
6 5 03-02-01
7 6 03 02 01
8 7 02 03, 01
9 8 04:05:06
10 9 02 3 2001 4:05:06:790AM
11 10 02-03-01
12 11 01/02/03
13 12 010203
14 13 03 02 2001 04:05:06:790
15 14 04:05:06:790
16 20 2001-02-03 04:05:06
17 21 2001-02-03 04:05:06.790
18 22 02/03/01 4:05:06 AM
19 23 2001-02-03
20 24 04:05:06
21 25 2001-02-03 04:05:06.790
22 100 02 3 2001 4:05AM
23 101 02/03/2001
24 102 2001.02.03
25 103 03/02/2001
26 104 03.02.2001
27 105 03-02-2001
28 106 03 02 2001
29 107 02 03, 2001
30 108 04:05:06
31 109 02 3 2001 4:05:06:790AM
32 110 02-03-2001
33 111 2001/02/03
34 112 20010203
35 113 03 02 2001 04:05:06:790
36 114 04:05:06:790
37 120 2001-02-03 04:05:06
38 121 2001-02-03 04:05:06.790
39 126 2001-02-03T04:05:06.790
40 127 2001-02-03T04:05:06.790
41 130 10 ?? ?????? 1421 4:05:06:790
42 131 10/11/1421 4:05:06:790AM

日本語のシステムの開発では、2, 8 (24, 108), 11, 12, 20 (120), 23, 102, 111, 112, ぐらいを使いそうです。

このうち、日付の形式が 2, 11, 12, 23, 102, 111, 112 の 7 種類、時刻が 8 (24, 108) の 1 種類、日時が 20 (120) の 1 種類になります。

上記のうちで覚えておいた方が良さそうなのは 時刻: 8 (24, 108)、日付: 111, 112 ぐらいかと思います。

ちなみに日付を年月日の順番に取得できるのは 2, 11, 12, 20, 21, 23, 25, 102, 111, 112, 120, 121, 126, 127 の 14 種類あります。

スタイルが 130 と 131 では日付が、よくわからない値になっています。

実行するまで気付きませんでしたが、ミリ秒の部分 (小数 3 けた目) が丸められて .789 から .790 になっています。これは DATETIME 型の変数 @DATETIME にデータを入れているために起こっています。
正しくミリ秒を 3 桁まで変換するためには DATETIME 型 ではなく DATETIME2(3) 型を使用する必要がありました。

ついでなので DATETIME 型にミリ秒まで指定した際に、ミリ秒の末尾が 0 ~ 9 の場合でどのように丸められるのかも調べておきます。

ここでは CONVERT 関数で文字列を日付 (DATETIME) に変換する SQL を実行しています。

実行結果

末尾 変換値 変換後の末尾
0 2001-02-03 04:05:06.780 0
1 2001-02-03 04:05:06.780 0
2 2001-02-03 04:05:06.783 3
3 2001-02-03 04:05:06.783 3
4 2001-02-03 04:05:06.783 3
5 2001-02-03 04:05:06.787 7
6 2001-02-03 04:05:06.787 7
7 2001-02-03 04:05:06.787 7
8 2001-02-03 04:05:06.787 7
9 2001-02-03 04:05:06.790 0

0, 1 の場合は 0 に丸められる。
2, 3, 4 の場合は 3 に丸められる。
5, 6, 7, 8, の場合は 7 に丸められる。
9 の場合は 0.1 秒に繰り上がって 0 に丸められる。

FORMAT 関数による日付の書式変換

ここまで CONVERT 関数を使って スタイル (書式) を指定して日付を文字列に変換してきましたが、SQL Server には FORMAT 関数という関数があり、この FORMAT 関数を使うことでも、書式を指定して日付を文字列に変換することができます。(この FORMAT 関数は SQL Server 2012 以降で使用することができます。)

実行結果

No 変換値
1 2001/02/03
2 2/3/2001
3 2001/02/03
4 2001年2月3日
5 Saturday, February 3, 2001
6 2001年2月3日

FORMAT 関数では書式指定文字列に、C# などと同様にカスタム書式指定文字列を指定することができます。

実行結果

No 変換値
1 20111213141516
2 2011/12/13 14:15:16
3 2011/12/13/2:15:16
4 12/13/2011 14:15
5 14:15:16.789

CONVERT 関数はスタイルを数値で指定しますが、FORMAT 関数では書式文字列を指定できるので、後で見たときにわかりやすいです。(実行しなくても、変換後の書式がわかります。)

まとめ

  • スタイルの種類は 35 種類。 (重複をカウントする場合は 42 種類。)
  • 2 桁の年が 13 種類、4 桁の年が 22 種類。
  • 時刻: 8 (24, 108)、日付: 111, 112 ぐらいを覚えておけばよさそう。
  • 日付を年月日の順番に取得できるのは 2, 11, 12, 20, 21, 23, 25, 102, 111, 112, 120, 121, 126, 127 の 14 種類。
  • スタイルが 130 と 131 は意図しない値が取得されるので、おそらく使わない。
  • CONVERT 関数より FORMAT 関数の方が使い勝手がいいかも。