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 になります。
|
|
SELECT CURRENT_TIMESTAMP AS RES_CURRENT_TIMESTAMP SELECT GETDATE() AS RES_GETDATE SELECT GETUTCDATE() AS RES_GETUTCDATE |
実行結果例
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) になります。
|
|
SELECT SYSDATETIME() AS RES_SYSDATETIME SELECT SYSUTCDATETIME() AS RES_SYSUTCDATETIME SELECT SYSDATETIMEOFFSET() AS RES_SYSDATETIMEOFFSET |
実行結果例
2010-03-15 11:32:49.6910495
CURRENT_TIMESTAMP, GETDATE, GETUTCDATE 関数は秒以下が３桁までの値 (yyyy-mm-dd hh:mm:ss.fff) を返しますが、SYSDATETIME, SYSUTCDATETIME は７桁までの値 (yyyy-mm-dd hh:mm:ss.fffffff) を返します。
ちなみに、SYSDATETIMEOFFSET という関数もあり、こちらはタイムゾーンのオフセット (UTC : 協定世界時との差) も一緒に返します。
|
|
SELECT SYSDATETIMEOFFSET() AS RES_SYSDATETIMEOFFSET |
戻り値のデータ型は datetimeoffset(7) になります。
実行結果例
2010-03-15 11:32:49.6910495 +09:00
指定した日付が属する月の末日を取得する EOMONTH
指定した日付を含む月の最後の日を取得したい時に使います。戻り値のデータ型は date になります。
|
|
SELECT EOMONTH('2001-02-03') |
実行結果
2001-02-28
日付の構成要素から日付を取得する関数 DATEFROMPARTS
指定した年、月、日の日付を取得したい時に使います。戻り値のデータ型は date になります。
|
|
SELECT DATEFROMPARTS(2001, 2, 3) |
実行結果
2001-02-03
日時の構成要素から日時を取得する関数１ DATETIMEFROMPARTS
DATETIMEFROMPARTS 関数は、年、月、日、時、分、秒、ミリ秒を指定して日時を取得したい時に使います。戻り値のデータ型は datetime になります。
|
|
SELECT DATETIMEFROMPARTS(2001, 2, 3, 4, 5, 6, 123) SELECT DATETIMEFROMPARTS(2001, 2, 3, 4, 5, 6, 0) |
実行結果
| No |
値 |
| 1 |
2001-02-03 04:05:06.123 |
| 2 |
2001-02-03 04:05:06.000 |
日時の構成要素から日時を取得する関数２ DATETIME2FROMPARTS
DATETIME2FROMPARTS 関数は、年、月、日、時、分、秒、小数部、小数部の有効桁数を指定して日時を取得したい時に使います。戻り値のデータ型は datetime2(小数部の有効桁数) になります。
|
|
SELECT DATETIME2FROMPARTS(2001, 2, 3, 4, 5, 6, 123, 3) SELECT DATETIME2FROMPARTS(2001, 2, 3, 4, 5, 6, 1234567, 7) SELECT DATETIME2FROMPARTS(2001, 2, 3, 4, 5, 6, 0, 0) |
実行結果
| 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 関数では、指定する小数部と小数部の有効桁数が一致している必要があります。
日時の構成要素から日時を取得する関数３ SMALLDATETIMEFROMPARTS
指定した年、月、日、時、分の日時を取得したい時に使います。戻り値のデータ型は smalldatetime になります。
|
|
SELECT SMALLDATETIMEFROMPARTS(2001, 2, 3, 4, 5) |
実行結果
2001-02-03 04:05:00
時刻の構成要素から時刻を取得する関数 TIMEFROMPARTS
|
|
SELECT TIMEFROMPARTS(1, 2, 3, 123, 3) SELECT TIMEFROMPARTS(1, 2, 3, 1234567, 7) SELECT TIMEFROMPARTS(1, 2, 3, 0, 0) |
実行結果
| No |
値 |
| 1 |
01:02:03.123 |
| 2 |
01:02:03.1234567 |
| 3 |
01:02:03 |
TIMEFROMPARTS 関数では、指定する小数部と小数部の有効桁数が一致している必要があります。
日付と時刻の要素 (日時の一部分) を抜き出した値を取得する関数
年を取得する YEAR
日時の年部分を取得したい時に使います。
|
|
SELECT YEAR('2001-02-03') AS RES_YEAR |
実行結果
2001
月を取得する MONTH
日時の月部分を取得したい時に使います。
|
|
SELECT MONTH('2001-02-03') AS RES_MONTH |
実行結果
2
日を取得する DAY
日時の日部分を取得したい時に使います。
|
|
SELECT DAY('2001-02-03') AS RES_DAY |
実行結果
3
要素を数値で取得する DATEPART
日時の指定した要素部分を抜き出した値を取得します。(日時の指定した要素を数値で取得します。)
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
/* 日時型の変数を宣言 */ DECLARE @DT AS DATETIME2 /* 変数に値を代入 */ SET @DT = '2001-02-03 04:05:06.1234567' /* 日時変数から各要素を抜き出す */ /* 年 */ SELECT DATEPART(yy, @DT) /* 月 */ SELECT DATEPART(mm, @DT) /* 日 */ SELECT DATEPART(dd, @DT) /* 時 */ SELECT DATEPART(hh, @DT) /* 分 */ SELECT DATEPART(mi, @DT) /* 秒 */ SELECT DATEPART(ss, @DT) /* ミリ秒 */ SELECT DATEPART(ms, @DT) /* マイクロ秒 */ SELECT DATEPART(mcs, @DT) /* 四半期 */ SELECT DATEPART(qq, @DT) /* 1/1からの日数 */ SELECT DATEPART(dayofyear, @DT) /* 週 */ SELECT DATEPART(wk, @DT) /* 曜日 */ SELECT DATEPART(dw, @DT) |
実行結果
| 日時要素 |
値 |
| 年 |
2001 |
| 月 |
2 |
| 日 |
3 |
| 時 |
4 |
| 分 |
5 |
| 秒 |
6 |
| ミリ秒 |
123 |
| マイクロ秒 |
123456 |
| 四半期 |
1 |
| 1/1からの日数 |
34 |
| 週 |
5 |
| 曜日 |
7 |
要素を名前 (文字列) で取得する DATENAME
日時の指定した要素を名前 (文字列) で取得します。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
/* 日時型の変数を宣言 */ DECLARE @DT AS DATETIME2 /* 変数に値を代入 */ SET @DT = '2001-02-03 04:05:06.1234567' /* 日時変数から各要素を抜き出す */ /* 年 */ SELECT DATENAME(yy, @DT) /* 月 */ SELECT DATENAME(mm, @DT) /* 日 */ SELECT DATENAME(dd, @DT) /* 時 */ SELECT DATENAME(hh, @DT) /* 分 */ SELECT DATENAME(mi, @DT) /* 秒 */ SELECT DATENAME(ss, @DT) /* ミリ秒 */ SELECT DATENAME(ms, @DT) /* マイクロ秒 */ SELECT DATENAME(mcs, @DT) /* 四半期 */ SELECT DATENAME(qq, @DT) /* 1/1からの日数 */ SELECT DATENAME(dayofyear, @DT) /* 週 */ SELECT DATENAME(wk, @DT) /* 曜日 */ SELECT DATENAME(dw, @DT) |
実行結果
| 日時要素 |
値 |
| 年 |
2001 |
| 月 |
02 |
| 日 |
3 |
| 時 |
4 |
| 分 |
5 |
| 秒 |
6 |
| ミリ秒 |
123 |
| マイクロ秒 |
123456 |
| 四半期 |
1 |
| 1/1からの日数 |
34 |
| 週 |
5 |
| 曜日 |
土曜日 |
日付と時刻を計算する関数
日時を加算する DATEADD
日時を加算した値を取得したい時に使います。
加算する値をマイナス (例えば -1) にすることで、日時を減算することもできます。
構文
DATEADD(日時要素, 加算する数値, 日時)
日時要素とは「年」「月」「日」「時」「分」「秒」などの日時の特定部分のことを表します。
|
|
SELECT /* 基準の日付 2001-01-01 に 1 年加算 */ DATEADD(YEAR, 1, '2001-01-01') AS ADD_ONE_YEAR, /* 基準の日付 2001-01-01 から 1 年減算 */ DATEADD(yy, -1, '2001-01-01') AS SUB_ONE_YEAR, /* 基準の日時 2001-01-01 01:01:01 に 2 時間加算 */ DATEADD(HOUR, 2, '2001-01-01 01:01:01 ') AS ADD_TWO_HOURS, /* 基準の日時 2001-01-01 01:01:01 から 2 時間減算 */ DATEADD(hh, -2, '2001-01-01 01:01:01 ') AS SUB_TWO_HOURS |
実行結果
| 列名 |
値 |
| 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(日時要素, 日時１, 日時２)
DATEDIFF_BIG(日時要素, 日時１, 日時２)
日時１と日時２の差 (日時２から日時１を差し引いた値) を返します。
DATEDIFF と DATEDIFF_BIG の違いは、戻り値が DATEDIFF の場合は int DATEDIFF_BIG の場合は bigint になる点です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
-- DATEDIFF /* 年 */ SELECT DATEDIFF(YEAR, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF(YEAR, '2002-02-03', '2001-02-03') AS [列２] /* 月 */ SELECT DATEDIFF(MONTH, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF(MONTH, '2002-02-03', '2001-02-03') AS [列２] /* 日 */ SELECT DATEDIFF(DAY, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF(DAY, '2002-02-03', '2001-02-03') AS [列２] /* 時 */ SELECT DATEDIFF(HOUR, '2001-02-03 00:00:00', '2002-02-03 00:00:00') AS [列１], DATEDIFF(HOUR, '2002-02-03 00:00:00', '2001-02-03 00:00:00') AS [列２] -- DATEDIFF_BIG /* 年 */ SELECT DATEDIFF_BIG(YEAR, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF_BIG(YEAR, '2002-02-03', '2001-02-03') AS [列２] /* 月 */ SELECT DATEDIFF_BIG(MONTH, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF_BIG(MONTH, '2002-02-03', '2001-02-03') AS [列２] /* 日 */ SELECT DATEDIFF_BIG(DAY, '2001-02-03', '2002-02-03') AS [列１], DATEDIFF_BIG(DAY, '2002-02-03', '2001-02-03') AS [列２] /* 時 */ SELECT DATEDIFF_BIG(HOUR, '2001-02-03 00:00:00', '2002-02-03 00:00:00') AS [列１], DATEDIFF_BIG(HOUR, '2002-02-03 00:00:00', '2001-02-03 00:00:00') AS [列２] |
実行結果
| 日時要素 |
列１ |
列２ |
| 年 |
1 |
-1 |
| 月 |
12 |
-12 |
| 日 |
365 |
-365 |
| 時 |
8760 |
-8760 |
365 日 * 24 時間 = 8760 時間
年齢の取得
DATEDIFF 関数を使えば、年齢を算出することも簡単にできます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
-- 生年月日 DECLARE @BIRTHDAY DATE = '1952-02-29'; -- 年齢を取得する日付 DECLARE @DATE DATE = '2001-02-03'; -- 年齢 DECLARE @AGE INT = (SELECT DATEDIFF(YEAR, @BIRTHDAY, @DATE)); -- 年齢調整 DECLARE @D VARCHAR(8) = CONVERT(VARCHAR(8), @DATE, 112); DECLARE @B VARCHAR(8) = CONVERT(VARCHAR(8), @BIRTHDAY, 112); DECLARE @D2 VARCHAR(8) = LEFT(@D, 4) + RIGHT(@B, 4); -- 年齢再設定 SET @AGE = CASE WHEN @D < @D2 THEN @AGE - 1 ELSE @AGE END -- 実行 SELECT @AGE |
実行結果
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」を返します。
|
|
SELECT ISDATE('') SELECT ISDATE('2011') SELECT ISDATE('201105') SELECT ISDATE('20110501') SELECT ISDATE('2011-05') SELECT ISDATE('2011-05-01') SELECT ISDATE('2011/05/01') SELECT ISDATE('2011 05 01') SELECT ISDATE('20110501101010') SELECT ISDATE('20110501 101010') SELECT ISDATE('20110501 10:10:10') SELECT ISDATE('2011-05-01 10:10:10') |
実行結果
| 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
日付と時刻のデータ型および関数 (Transact-SQL)
MySQL の日時関数については、以下の記事が参考になります。
MySQL 日付と時刻の関数一覧
MySQL で日付、時刻を操作するための、主な SQL 関数です。
MySQL には SQL Server と同等以上の様々関数が用意...
SQL Server の他の関数一覧については、以下の記事を参照してください。
SQLServer 文字列関数一覧
SQLServerで文字列を操作する際に使用する主なSQL関数です。
文字列から一部の文字列を抜き出したい。
文字列を置...
SQL Server 集計関数一覧
SQLServerで集計する際に使う主なSQL関数です。
合計を取得したい。
平均値を取得したい。
件数を取得したい。...
おすすめの書籍
