今川館

都内勤務の地味OLです

SQLServerの文字列と日付の暗黙的型変換

文字列型からDATE/DATETIME型への暗黙的型変換

SQLServerは文字列型の値をDATEやDATETIMEで評価する必要がある場合は暗黙的に型変換を試みる。

クエリ1

SELECT MAX(foo) foo FROM (
    SELECT '2015-2-13' foo
    UNION ALL SELECT '2015-2-14' foo
    UNION ALL SELECT CONVERT(DATE, '2015-2-12') foo
) x

結果

foo
2015-02-15

クエリ1の結果セットのfooはDATE型であり、文字列型ではない。
だからこんなことをするとエラーになる。

クエリ2

SELECT MAX(foo) + 'a' foo FROM (
    SELECT '2015-2-13' foo
    UNION ALL SELECT '2015-2-14' foo
    UNION ALL SELECT CONVERT(DATE, '2015-2-12') foo
) x

結果

データ型 date と varchar は add 演算子では互換性がありません。

日付・日時フォーマットを守っていないと当然エラー

当然ながら文字列型の値が日付・日時フォーマットに則っていないとエラーになる。

クエリ3

SELECT MAX(foo) foo FROM (
    SELECT '2015-2-13' foo
    UNION ALL SELECT '2015-2-14?' foo
    UNION ALL SELECT CONVERT(DATE, '2015-2-15') foo
) x

結果

文字列から日付と時刻、またはそのいずれかへの変換中に、変換が失敗しました。

空文字列をDATE型に変換すると'1900-01-01'と評価する

驚いたのが、空文字列もDATE/DATETIME型に変換できてしまう。

クエリ4

SELECT CONVERT(DATE, '') foo, CONVERT(DATETIME, '') bar

結果

foo	bar
1900-01-01	1900-01-01 00:00:00.000

DATE/DATETIMEからVARCHAR型への変換も暗黙的に行われる

更に驚いたことだが、VARCHAR -> DATE/DATETIMEの変換だけでなく、DATE/DATETIME -> VARCHARの型変換も暗黙的に行われる。

クエリ5

DECLARE
    @x DATE, @y VARCHAR(10), @z VARCHAR(8)
BEGIN
    SET @x = '2015-2-22';  -- DATE型変数に代入した時点でVARCHAR -> DATEに変換
    SET @y = @x;  -- VARCHAR型変数に代入したのでDATE -> VARCHARに戻る
    PRINT @y;
END

結果

2015-02-22

桁長に注意

ちなみに上記例の@zの変数に@xを代入すると思わぬ結果が出るので注意。

クエリ6

DECLARE
    @x DATE, @y VARCHAR(10), @z VARCHAR(8)
BEGIN
    SET @x = '2015-2-22';  -- DATE型変数に代入した時点でVARCHAR -> DATEに変換
    SET @y = @x;
    SET @z = @x;  -- VARCHAR型変数に代入したのでDATE -> VARCHARに戻る
    PRINT @z;
END

結果

2015-02-

はい、この通り。@zは8桁で宣言してあるので先頭から9桁以降は文字が落ちてしまう。