SQL Serverで日付型を文字列に変換する
日付型を文字列に変換する場合、VBではFORMAT関数、JAVAではToStringメソッドでyyyyMMddのような日付書式を指定して変換しますが、SQL Serverでは日付書式を指定した文字列への変換ができません。
そこで、SQL Serverで日付型を文字列に変換する方法を紹介します。
CONVERT関数
SQL Serverで日付型を文字列に変換するにはCONVERT関数を使用します。
コーディング例
01 02 03 04 05 06 07 08 09 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 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 | BEGIN DECLARE @DATE DATETIME = CONVERT(DATETIME,'2019-01-02 03:04:05:678',121); SELECT 100 AS [Style], CONVERT(VARCHAR,@DATE,100) AS [日付] UNION SELECT 101 AS [Style], CONVERT(VARCHAR,@DATE,101) AS [日付] UNION SELECT 102 AS [Style], CONVERT(VARCHAR,@DATE,102) AS [日付] UNION SELECT 103 AS [Style], CONVERT(VARCHAR,@DATE,103) AS [日付] UNION SELECT 104 AS [Style], CONVERT(VARCHAR,@DATE,104) AS [日付] UNION SELECT 105 AS [Style], CONVERT(VARCHAR,@DATE,105) AS [日付] UNION SELECT 106 AS [Style], CONVERT(VARCHAR,@DATE,106) AS [日付] UNION SELECT 107 AS [Style], CONVERT(VARCHAR,@DATE,107) AS [日付] UNION SELECT 108 AS [Style], CONVERT(VARCHAR,@DATE,108) AS [日付] UNION SELECT 109 AS [Style], CONVERT(VARCHAR,@DATE,109) AS [日付] UNION SELECT 110 AS [Style], CONVERT(VARCHAR,@DATE,110) AS [日付] UNION SELECT 111 AS [Style], CONVERT(VARCHAR,@DATE,111) AS [日付] UNION SELECT 112 AS [Style], CONVERT(VARCHAR,@DATE,112) AS [日付] UNION SELECT 113 AS [Style], CONVERT(VARCHAR,@DATE,113) AS [日付] UNION SELECT 114 AS [Style], CONVERT(VARCHAR,@DATE,114) AS [日付] UNION SELECT 120 AS [Style], CONVERT(VARCHAR,@DATE,120) AS [日付] UNION SELECT 121 AS [Style], CONVERT(VARCHAR,@DATE,121) AS [日付] UNION SELECT 126 AS [Style], CONVERT(VARCHAR,@DATE,126) AS [日付] UNION SELECT 127 AS [Style], CONVERT(VARCHAR,@DATE,127) AS [日付] ; SELECT 0 AS [Style], CONVERT(VARCHAR,@DATE,0) AS [日付] UNION SELECT 1 AS [Style], CONVERT(VARCHAR,@DATE,1) AS [日付] UNION SELECT 2 AS [Style], CONVERT(VARCHAR,@DATE,2) AS [日付] UNION SELECT 3 AS [Style], CONVERT(VARCHAR,@DATE,3) AS [日付] UNION SELECT 4 AS [Style], CONVERT(VARCHAR,@DATE,4) AS [日付] UNION SELECT 5 AS [Style], CONVERT(VARCHAR,@DATE,5) AS [日付] UNION SELECT 6 AS [Style], CONVERT(VARCHAR,@DATE,6) AS [日付] UNION SELECT 7 AS [Style], CONVERT(VARCHAR,@DATE,7) AS [日付] UNION SELECT 8 AS [Style], CONVERT(VARCHAR,@DATE,8) AS [日付] UNION SELECT 9 AS [Style], CONVERT(VARCHAR,@DATE,9) AS [日付] UNION SELECT 10 AS [Style], CONVERT(VARCHAR,@DATE,10) AS [日付] UNION SELECT 11 AS [Style], CONVERT(VARCHAR,@DATE,11) AS [日付] UNION SELECT 12 AS [Style], CONVERT(VARCHAR,@DATE,12) AS [日付] UNION SELECT 13 AS [Style], CONVERT(VARCHAR,@DATE,13) AS [日付] UNION SELECT 14 AS [Style], CONVERT(VARCHAR,@DATE,14) AS [日付] UNION SELECT 20 AS [Style], CONVERT(VARCHAR,@DATE,20) AS [日付] UNION SELECT 21 AS [Style], CONVERT(VARCHAR,@DATE,21) AS [日付] ;END |
CONVERT関数の第1引数には変換先のデータ型を指定しますので「VARCHAR」を指定しています。
第2引数には変換元の値を指定しますので、DATETIME型の変数「@DATE」を指定しています。
第3引数には変換するスタイルを指定します。スタイルはあらかじめ決められた値を指定します。
実行結果
| Style | 日付 |
|---|---|
| 100 | 01 2 2019 3:04AM |
| 101 | 01/02/2019 |
| 102 | 2019.01.02 |
| 103 | 02/01/2019 |
| 104 | 02.01.2019 |
| 105 | 02-01-2019 |
| 106 | 02 01 2019 |
| 107 | 01 02, 2019 |
| 108 | 03:04:05 |
| 109 | 01 2 2019 3:04:05:677AM |
| 110 | 01-02-2019 |
| 111 | 2019/01/02 |
| 112 | 20190102 |
| 113 | 02 01 2019 03:04:05:677 |
| 114 | 03:04:05:677 |
| 120 | 2019-01-02 03:04:05 |
| 121 | 2019-01-02 03:04:05.677 |
| 126 | 2019-01-02T03:04:05.677 |
| 127 | 2019-01-02T03:04:05.677 |
| Style | 日付 |
|---|---|
| 0 | 01 2 2019 3:04AM |
| 1 | 01/02/19 |
| 2 | 19.01.02 |
| 3 | 02/01/19 |
| 4 | 02.01.19 |
| 5 | 02-01-19 |
| 6 | 02 01 19 |
| 7 | 01 02, 19 |
| 8 | 03:04:05 |
| 9 | 01 2 2019 3:04:05:677AM |
| 10 | 01-02-19 |
| 11 | 19/01/02 |
| 12 | 190102 |
| 13 | 02 01 2019 03:04:05:677 |
| 14 | 03:04:05:677 |
| 20 | 2019-01-02 03:04:05 |
| 21 | 2019-01-02 03:04:05.677 |
一般的によく使われるスタイルは「102」「111」「121」あたりでしょうか。
スタイルを100未満にすると西暦2桁になるようです。
以上、「SQL Serverで日付型を文字列に変換する」でした。
注意！！
SQL Server 2017以降ではFORMAT関数が使用できるようになりました。
0