SQL Server 日付と時刻の関数一覧

SQLServerロゴ

SQL Server で日付や時刻を操作する際に使う、主な SQL 関数です。

  • 実行時の日付が取得したい。
  • 特定の日時から数日後 (数時間後)、数日前 (数時間前)の日時が取得したい。
  • 任意の日付の曜日が取得したい。
  • 任意の日付の月の末日が取得したい。
  • 生年月日から年齢を計算したい。

などなど…
関数を効果的に活用することで、データを取得した後に加工しなくても、日時に関する要件・仕様を SQL で満たすことができます。

SQL Server の日付・時刻を操作する主な関数

関数名 概要
CURRENT_TIMESTAMP 現在の日時を返す (ANSI SQL)
DATEADD 日時を加算 (減算) した値を返す
DATEDIFF 日時の差を取得する
DATEDIFF_BIG 日時の差を取得する
DATEFROMPARTS 指定した年、月、日の日付を返す
DATENAME 日時の指定した要素部分の名前 (文字列) を返す
DATEPART 日時の指定した要素部分の値 (数値) を返す
DATETIME2FROMPARTS 指定した年、月、日、時、分、秒、秒の小数部、小数部の有効桁数の日付を返す
DATETIMEFROMPARTS 指定した年、月、日、時、分、秒、ミリ秒の日付を返す
DAY 日時の日部分を返す
EOMONTH 指定した日付を含む月の最後の日 (末日) を返す
GETDATE 現在の日時を返す
GETUTCDATE 現在の日時 (UTC : 協定世界時) を返す
ISDATE 値が日付 (日時) かどうか (日付・日時としての妥当性) を返す
MONTH 日時の月部分を返す
SMALLDATETIMEFROMPARTS 指定した年、月、日、時、分の日付を返す
TIMEFROMPARTS 指定した時、分、秒、秒の小数部、小数部の有効桁数の日付を返す
YEAR 日時の年部分を返す

日付と時刻を取得する関数

CURRENT_TIMESTAMP, GETDATE, GETUTCDATE

現在の日時を取得したい時に使います。
CURRENT_TIMESTAMP は ANSI 標準の関数です。
GETUTCDATE は UTC (Coordinated Universal Time) : 協定世界時を取得します。

戻り値のデータ型は datetime になります。

実行結果例
2010-03-15 11:32:49.691

SQL Server では GETDATE が最もよく使われているのではないかと思います。
ANSI SQL にこだわる or 他のデータベースと合わせるなら、CURRENT_TIMESTAMP を使った方がいいかもしれません。

データ型が datetime の作成日列をテーブルに用意する際に、既定値として getdate() を設定しておくと、わざわざ登録する必要がないので楽だったりします。

高精度の日時を取得する関数

GETDATE, GETUTCDATE と似た関数に SYSDATETIME, SYSUTCDATETIME があります。
SYS から始まるこれらの関数を使うことで、GETDATE, GETUTCDATE よりも精度が高い日時を取得することができます。

戻り値のデータ型は datetime2(7) になります。

実行結果例
2010-03-15 11:32:49.6910495

CURRENT_TIMESTAMP, GETDATE, GETUTCDATE 関数は秒以下が3桁までの値 (yyyy-mm-dd hh:mm:ss.fff) を返しますが、SYSDATETIME, SYSUTCDATETIME は7桁までの値 (yyyy-mm-dd hh:mm:ss.fffffff) を返します。

ちなみに、SYSDATETIMEOFFSET という関数もあり、こちらはタイムゾーンのオフセット (UTC : 協定世界時との差) も一緒に返します。

戻り値のデータ型は datetimeoffset(7) になります。

実行結果例
2010-03-15 11:32:49.6910495 +09:00

指定した日付が属する月の末日を取得する EOMONTH

指定した日付を含む月の最後の日を取得したい時に使います。戻り値のデータ型は date になります。

実行結果

2001-02-28

日付の構成要素から日付を取得する関数 DATEFROMPARTS

指定した年、月、日の日付を取得したい時に使います。戻り値のデータ型は date になります。

実行結果

2001-02-03

日時の構成要素から日時を取得する関数1 DATETIMEFROMPARTS

DATETIMEFROMPARTS 関数は、年、月、日、時、分、秒、ミリ秒を指定して日時を取得したい時に使います。戻り値のデータ型は datetime になります。

実行結果

No
1 2001-02-03 04:05:06.123
2 2001-02-03 04:05:06.000

日時の構成要素から日時を取得する関数2 DATETIME2FROMPARTS

DATETIME2FROMPARTS 関数は、年、月、日、時、分、秒、小数部、小数部の有効桁数を指定して日時を取得したい時に使います。戻り値のデータ型は datetime2(小数部の有効桁数) になります。

実行結果

No
1 2001-02-03 04:05:06.123
2 2001-02-03 04:05:06.1234567
3 2001-02-03 04:05:06

DATETIME2FROMPARTS 関数では、指定する小数部と小数部の有効桁数が一致している必要があります。

日時の構成要素から日時を取得する関数3 SMALLDATETIMEFROMPARTS

指定した年、月、日、時、分の日時を取得したい時に使います。戻り値のデータ型は smalldatetime になります。

実行結果

2001-02-03 04:05:00

時刻の構成要素から時刻を取得する関数 TIMEFROMPARTS

実行結果

No
1 01:02:03.123
2 01:02:03.1234567
3 01:02:03

TIMEFROMPARTS 関数では、指定する小数部と小数部の有効桁数が一致している必要があります。

日付と時刻の要素 (日時の一部分) を抜き出した値を取得する関数

年を取得する YEAR

日時の年部分を取得したい時に使います。

実行結果

2001

月を取得する MONTH

日時の月部分を取得したい時に使います。

実行結果

2

日を取得する DAY

日時の日部分を取得したい時に使います。

実行結果

3

要素を数値で取得する DATEPART

日時の指定した要素部分を抜き出した値を取得します。(日時の指定した要素を数値で取得します。)

実行結果

日時要素
2001
2
3
4
5
6
ミリ秒 123
マイクロ秒 123456
四半期 1
1/1からの日数 34
5
曜日 7

要素を名前 (文字列) で取得する DATENAME

日時の指定した要素を名前 (文字列) で取得します。

実行結果

日時要素
2001
02
3
4
5
6
ミリ秒 123
マイクロ秒 123456
四半期 1
1/1からの日数 34
5
曜日 土曜日

日付と時刻を計算する関数

日時を加算する DATEADD

日時を加算した値を取得したい時に使います。
加算する値をマイナス (例えば -1) にすることで、日時を減算することもできます。

構文
DATEADD(日時要素, 加算する数値, 日時)
日時要素とは「年」「月」「日」「時」「分」「秒」などの日時の特定部分のことを表します。

実行結果

列名
ADD_ONE_YEAR 2002-01-01 00:00:00.000
SUB_ONE_YEAR 2000-01-01 00:00:00.000
ADD_TWO_HOURS 2001-01-01 03:01:01.000
SUB_TWO_HOURS 2000-12-31 23:01:01.000

日時の差を取得する DATEDIFF

日時の差を取得したい時に使います。

構文
DATEDIFF(日時要素, 日時1, 日時2)
DATEDIFF_BIG(日時要素, 日時1, 日時2)

日時1と日時2の差 (日時2から日時1を差し引いた値) を返します。
DATEDIFF と DATEDIFF_BIG の違いは、戻り値が DATEDIFF の場合は int DATEDIFF_BIG の場合は bigint になる点です。

実行結果

日時要素 列1 列2
1 -1
12 -12
365 -365
8760 -8760
365 日 * 24 時間 = 8760 時間

年齢の取得

DATEDIFF 関数を使えば、年齢を算出することも簡単にできます。

実行結果

48

日付と時刻の関数で使用する日時要素 (日時の一部分)

日時要素 正式形 省略形
year yy, yyyy
四半期 quarter qq, q
month mm, m
対象年の通算日
(1/1からの日数)
dayofyear dy, y
day dd, d
week wk, ww
曜日 weekday dw, w
hour hh
minute mi, n
second ss, s
ミリ秒 millisecond ms
マイクロ秒 microsecond mcs
ナノ秒 nanosecond ns

日付・日時かどうかを検査する関数

日時の検証結果を取得する ISDATE

値が日付 (日時) であるかどうかを検証したい時に使います。
ISDATE 関数は検証した値が日付 (日時) であれば「1」を、その他は「0」を返します。

実行結果

No Boolean
1 0 false
2 1 true
3 1 true
4 1 true
5 0 false
6 1 true
7 1 true
8 1 true
9 0 false
10 0 false
11 1 true
12 1 true
‘2011’ は「2011-01-01」’201105′ は「2020-11-05」と認識されて検証されるので注意!

参考URL
日付と時刻のデータ型および関数

MySQL の日時関数については、以下のページが参考になります。

MySQL で日付、時刻を操作するための、主な SQL 関数です。 MySQL には SQL Server と同等以上の様々関数が用意...