よく使われる日付と時刻の関数 2 - DATEADD, DATEDIFF, ISDATE

前回は「よく使われる日付と時刻の関数 1 - GETDATE, YEAR, MONTH, DAY, DATEPART 」で、よく使われる日付と時刻の組み込み文字列関数のうち、GETDATE, YEAR, MONTH, DAY, DATEPART を使ってみました。

今回は、日付と時刻の関数の DATEADD, DATEDIFF, ISDATE を使ってみましょう。

DATEADD ファンクション

DATEADD ファンクションは、日時の指定した部分に、指定した数を足した日時を返す関数です。構文は次の通りです。

DATEADD ([追加する部分], [追加する数], [日時] )

[追加する部分] には、次のような指定が可能です。カッコの中のように省略して指定することも可能です。

年 - year (yy, yyyy)
月 - month (mm, m)
日 - day (dd, d)
時 - hour (hh)
分 - minute (mi, n)
秒 - second (ss, s)

※ よく使うものだけをピックアップしています。

[追加する数] は整数で指定し、マイナスの値でも大丈夫です。

例えば、2017-07-31 22:12:33.530 に、DATEADD でそれぞれの日時の「部分」に「 1 」を追加すると次のようになります。

DECLARE @Input DATETIME = '2017-07-31 22:12:33.530';

SELECT  DATEADD(yy, 1, @Input) AS yy_Input,
        DATEADD(mm, 1, @Input) AS mm_Input,
        DATEADD(dd, 1, @Input) AS dd_Input;

SELECT  DATEADD(hh, 1, @Input) AS hh_Input,
        DATEADD(mi, 1, @Input) AS mi_Input,
        DATEADD(ss, 1, @Input) AS ss_Input;

[ 実行結果 ]

それぞれ指定した部分に 1 が追加された日付が返ってきていますね。

DATEDIFF ファンクション

DATEDIFF ファンクションは、[日時 1] と [日時 2] の日時の差分を、[戻り値の単位] で指定した単位の整数で返します。構文は次の通りです。

DATEDIFF ([戻り値の単位], [日時 1], [日時 2])

[戻り値の単位] には、次のような指定が可能です。カッコの中のように省略して指定することも可能です。

年 - year (yy, yyyy)
月 - month (mm, m)
日 - day (dd, d)
時 - hour (hh)
分 - minute (mi, n)
秒 - second (ss, s)

※ よく使うものだけをピックアップしています。

例えば、 2017-07-31 22:12:33.530 と 2017-08-10 02:10:55.320 の差分を、それぞれの [戻り値の単位] を取得すると、次のようになります。

DECLARE @Input1 DATETIME = '2017-07-31 22:12:33.530',
        @Input2 DATETIME = '2017-08-10 02:10:55.320';

SELECT  DATEDIFF(yy, @Input1, @Input2) AS yy_Input,
        DATEDIFF(mm, @Input1, @Input2) AS mm_Input,
        DATEDIFF(dd, @Input1, @Input2) AS dd_Input,
        DATEDIFF(hh, @Input1, @Input2) AS hh_Input,
        DATEDIFF(mi, @Input1, @Input2) AS mi_Input,
        DATEDIFF(ss, @Input1, @Input2) AS ss_Input;

[ 実行結果 ]

ISDATE ファンクション

ISDATE ファンクションは、指定した文字列が、有効な DATE 型、TIME 型、もしくは DATETIME 型であった時に 1 を返す関数です。構文は次の通りです。

ISDATE ( [文字列] )

DATETIME2 型の時は 0 を返します。 1753-01-01 から 9999-12-31 の日付でないと 0 が返ります。

例えば、次のような 5 つの Input に対し、ISDATE のチェックすると、次のようになります。

DECLARE @Input1 VARCHAR(30) = '0001-01-01',
        @Input2 VARCHAR(30) = '1/13/2018 20:00',
        @Input3 VARCHAR(30) = '13/1/2018 20:00',
        @Input4 VARCHAR(30) = '21:40',
        @Input5 VARCHAR(30) = 'ABC';

SELECT  ISDATE(@Input1),
        ISDATE(@Input2),
        ISDATE(@Input3),
        ISDATE(@Input4),
        ISDATE(@Input5);

[ 実行結果 ]

@Input1 の '0001-01-01' は 1753-01-01 から 9999-12-31 の範囲外なので 0、 @Input5 の 'ABC' も、もちろん 0 が返ってきています。

注目していただきたいのは、@Input3 の '13/1/2018 20:00' に 0 が返っている点です。 dd/MM/yyyy のように、day から日付を始める国もあると思いますが、Invalid と判断されています。

ISDATE の戻り値は DATEFORMAT や LANGUAGE の設定に依存します。

私の環境では、次のように LANGUAGE が us_english、DATEFORMAT が mdy になっているため、dd/MM/yyyy のフォーマットの日付には 0 が返ってきました。

SET LANGUAGE British; で言語を英語 (U.K.)に変更して、先ほどを同じインプットで ISDATE を試してみると、@Input2 と @Input3 の戻り値が反対になっていることがわかります。

[ 実行結果 ]

次は、よく使われる順位付け関数の ROW_NUMBER ファンクションを使ってみましょう。

次へよく使われる順位付け関数 1 - ROW_NUMBER