SQL Server で使う日付と時刻を操作する関数

スポンサーリンク

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

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

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

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

関数名 概要
CURRENT_TIMESTAMP 現在の日時を返す (ANSI SQL)
GETDATE 現在の日時を返す
GETUTCDATE 現在の日時 (UTC : 協定世界時) を返す
DATEADD 日時を加算 (減算) した値を返す
DATEDIFF 日時の差を取得する
DATENAME 日時の指定した要素部分の名前 (文字列) を返す
DATEPART 日時の指定した要素部分の値 (数値) を返す
DAY 日時の日部分を返す
ISDATE 値が日付 (日時) かどうか (日付・日時としての妥当性) を返す
MONTH 日時の月部分を返す
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

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

年を取得する 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)

日時1と日時2の差 (日時2から日時1を差し引いた値) を返します。

実行結果

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

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

日時要素 正式形 省略形
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」と認識されて検証されるので注意!