SQL Serverで日付型を文字列に変換する

2019-09-21SQLServer

日付型を文字列に変換する場合、VBではFORMAT関数、JAVAではToStringメソッドでyyyyMMddのような日付書式を指定して変換しますが、SQL Serverでは日付書式を指定した文字列への変換ができません。
そこで、SQL Serverで日付型を文字列に変換する方法を紹介します。

CONVERT関数

SQL Serverで日付型を文字列に変換するにはCONVERT関数を使用します。

コーディング例

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関数が使用できるようになりました。

 

株式会社システムトラスト

人材募集中です。

システムトラストでは、システムエンジニア、プログラマーなどを随時募集中です。気軽にご相談ください。

お問合せ