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/yyyy2 102 ANSI 2 = yy.mm.dd
102 = yyyy.mm.dd3 103 イギリス/フランス 3 = dd/mm/yy
103 = dd/mm/yyyy4 104 German 4 = dd.mm.yy
104 = dd.mm.yyyy5 105 Italian 5 = dd-mm-yy
105 = dd-mm-yyyy6 106 (1) – 6 = dd mon yy
106 = dd mon yyyy7 107 (1) – 7 = Mon dd, yy
107 = Mon dd, yyyy8 または 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-yyyy11 111 日本 11 = yy/mm/dd
111 = yyyy/mm/dd12 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 に変換すると、タイム ゾーン オフセットが結果に追加されます。
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 を実行してみます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
DECLARE @DATETIME DATETIME; SET @DATETIME = '2001-02-03 04:05:06.789'; SELECT CONVERT(VARCHAR, @DATETIME, 0); SELECT CONVERT(VARCHAR, @DATETIME, 1); SELECT CONVERT(VARCHAR, @DATETIME, 2); SELECT CONVERT(VARCHAR, @DATETIME, 3); SELECT CONVERT(VARCHAR, @DATETIME, 4); SELECT CONVERT(VARCHAR, @DATETIME, 5); SELECT CONVERT(VARCHAR, @DATETIME, 6); SELECT CONVERT(VARCHAR, @DATETIME, 7); SELECT CONVERT(VARCHAR, @DATETIME, 8); SELECT CONVERT(VARCHAR, @DATETIME, 9); SELECT CONVERT(VARCHAR, @DATETIME, 10); SELECT CONVERT(VARCHAR, @DATETIME, 11); SELECT CONVERT(VARCHAR, @DATETIME, 12); SELECT CONVERT(VARCHAR, @DATETIME, 13); SELECT CONVERT(VARCHAR, @DATETIME, 14); SELECT CONVERT(VARCHAR, @DATETIME, 20); SELECT CONVERT(VARCHAR, @DATETIME, 21); SELECT CONVERT(VARCHAR, @DATETIME, 22); SELECT CONVERT(VARCHAR, @DATETIME, 23); SELECT CONVERT(VARCHAR, @DATETIME, 24); SELECT CONVERT(VARCHAR, @DATETIME, 25); SELECT CONVERT(VARCHAR, @DATETIME, 100); SELECT CONVERT(VARCHAR, @DATETIME, 101); SELECT CONVERT(VARCHAR, @DATETIME, 102); SELECT CONVERT(VARCHAR, @DATETIME, 103); SELECT CONVERT(VARCHAR, @DATETIME, 104); SELECT CONVERT(VARCHAR, @DATETIME, 105); SELECT CONVERT(VARCHAR, @DATETIME, 106); SELECT CONVERT(VARCHAR, @DATETIME, 107); SELECT CONVERT(VARCHAR, @DATETIME, 108); SELECT CONVERT(VARCHAR, @DATETIME, 109); SELECT CONVERT(VARCHAR, @DATETIME, 110); SELECT CONVERT(VARCHAR, @DATETIME, 111); SELECT CONVERT(VARCHAR, @DATETIME, 112); SELECT CONVERT(VARCHAR, @DATETIME, 113); SELECT CONVERT(VARCHAR, @DATETIME, 114); SELECT CONVERT(VARCHAR, @DATETIME, 120); SELECT CONVERT(VARCHAR, @DATETIME, 121); SELECT CONVERT(VARCHAR, @DATETIME, 126); SELECT CONVERT(VARCHAR, @DATETIME, 127); SELECT CONVERT(VARCHAR, @DATETIME, 130); SELECT CONVERT(VARCHAR, @DATETIME, 131); |
実行結果
| 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) 型を使用する必要がありました。
|
1 2 |
DECLARE @DATETIME DATETIME2(3); SET @DATETIME = '2001-02-03 04:05:06.789'; |
ついでなので DATETIME 型にミリ秒まで指定した際に、ミリ秒の末尾が 0 ～ 9 の場合でどのように丸められるのかも調べておきます。
ここでは CONVERT 関数で文字列を日付 (DATETIME) に変換する SQL を実行しています。
|
1 2 3 4 5 6 7 8 9 10 |
SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.780'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.781'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.782'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.783'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.784'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.785'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.786'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.787'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.788'); SELECT CONVERT(DATETIME, '2001-02-03 04:05:06.789'); |
実行結果
| 末尾 | 変換値 | 変換後の末尾 |
|---|---|---|
| 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 以降で使用することができます。)
|
1 2 3 4 5 6 7 8 |
DECLARE @DATETIME DATETIME; SET @DATETIME = '2001-02-03 04:05:06.789'; SELECT FORMAT(@DATETIME, 'd', 'ja-JP'); SELECT FORMAT(@DATETIME, 'd', 'en-US'); SELECT FORMAT(@DATETIME, 'd'); SELECT FORMAT(@DATETIME, 'D', 'ja-JP'); SELECT FORMAT(@DATETIME, 'D', 'en-US'); SELECT FORMAT(@DATETIME, 'D'); |
実行結果
| 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# などと同様にカスタム書式指定文字列を指定することができます。
|
1 2 3 4 5 6 7 |
DECLARE @DATETIME DATETIME2(3); SET @DATETIME = '2011-12-13 14:15:16.789'; SELECT FORMAT(@DATETIME, 'yyyyMMddHHmmss'); SELECT FORMAT(@DATETIME, 'yyyy/MM/dd HH:mm:ss'); SELECT FORMAT(@DATETIME, 'yyyy/M/d/h:m:s'); SELECT FORMAT(@DATETIME, 'MM/dd/yyyy HH:mm'); SELECT FORMAT(@DATETIME, 'HH:mm:ss.fff'); |
実行結果
| 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 関数の方が使い勝手がいいかも。
