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

2019/09/212021/02/04データベースSQLServer

日付型を文字列に変換する場合、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引数には変換するスタイルを指定します。スタイルはあらかじめ決められた値を指定します。

実行結果

一般的によく使われるスタイルは「102」「111」「121」あたりでしょうか。
スタイルを100未満にすると西暦2桁になるようです。

以上、「SQL Serverで日付型を文字列に変換する」でした。

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

お問合せ