[SQL Server] 日時を操作する関数の使い方まとめ [DATE][TIME]

SQL Server で日付をや時間を操作する関数

SQL Server で日付や時間を操作する方法を調べました。例えばある日付から3日後の日付を取得したり、2つの日付の差分日数を計算したりする方法をSQL上で関数を使って実現します。

日付や時間を表すデータ型には以下のようなものがあります。

• date
• datetime
• datetimeoffset
• datetime2
• smalldatetime
• time

これらのデータを対象に操作できる関数を紹介します。

日時の加減算(DATEADD)

DATEADD (Transact-SQL) – SQL Server | Microsoft Docs

DATEADD 関数は、ある日付(DATE)ないしは日時(DATETIME)のデータに対して指定した値を加減算することができる関数です。

構文はこんな感じです。

DATEADD(要素, 数量, 対象日時)

要素には加算する数量の種類を指定します。指定の仕方をまとめます。

dayofyear と weekday は加減算では関係ないです。dayと同じだと思います。

--現在日時の1か月後の日時を取得
SELECT DATEADD(MONTH, 1, GETDATE())
--現在日時の3日前の日時を取得
SELECT DATEADD(d, -3, GETDATE())

上記のように使います。GETDATE() は現在日時を取得する関数です。数量にマイナスを指定すると減算処理になります。

ちなみに DATE DATETIME 以外にも、TIME 型(時刻データ)に対してもDATEADD関数を利用できます。その場合、加算できるのは時間以下の要素についてのみです。

--2時間後(14:34:56.7890)
DECLARE @time time(4) = '12:34:56.7890';  
SELECT     DATEADD(d, 2, @time)

時刻データに日を加算すると「日付構成要素 day はデータ型 time の日付関数 dateadd ではサポートされていません。」というようなエラーで怒られます。

--TIME型に日は加算できないのでエラー
--日付構成要素 day はデータ型 time の日付関数 dateadd ではサポートされていません。
DECLARE @time time(4) = '12:34:56.7890';  
SELECT     DATEADD(d, 2, @time)

日時の部分(日, 分など)を取得する関数(DATEPART)

DATEPART (Transact-SQL) – SQL Server | Microsoft Docs

日時データの一部を取り出したい場合があります。例えば、ある日付データの日付部分だけを取り出したい場合などです。そんな時に使えるのが DATEPART 関数です。文字通り日付からパーツを抜き出す関数です。

DATEPART(要素, 対象日時)

要素で指定できるものは DATEADD と同じです。指定した要素が整数値として返されます。

--月(11)を取得
SELECT DATEPART(MONTH, '2019-11-10')
--現在日時の秒数を取得
SELECT DATEPART(s, GETDATE())

時刻データに対して分数や秒数も取得できます。

--分(34)を取得
DECLARE @time time(4) = '12:34:56.7890';  
SELECT DATEPART(mi, @time)

曜日は1(日曜日)～7(土曜日)です。

--2019-11-10(日)
SELECT 
  DATEPART(WEEKDAY, '2019-11-10')   --1
  , DATEPART(WEEKDAY, '2019-11-11') --2
  , DATEPART(WEEKDAY, '2019-11-12') --3
  , DATEPART(WEEKDAY, '2019-11-13') --4
  , DATEPART(WEEKDAY, '2019-11-14') --5
  , DATEPART(WEEKDAY, '2019-11-15') --6
  , DATEPART(WEEKDAY, '2019-11-16') --7

1年で何日目、1か月で何週目かを取得する

加減算で使わなかった dayofyear と weekday はここで活躍します。1年で何日目の日付か、1か月で何週間目の日付かを取得するのに使います。

SELECT 
    DATEPART(dy, '2020-12-31')
    , DATEPART(w, '2020-12-31')
--2020-12-31 は 366日目, 5週間目

YEAR, MONTH, DAY 関数

DATEPART 関数では日時データを分解して詳しいデータを取得できますが、年月日については個別で取得する関数が用意されています。それぞれ YEAR, MONTH, DAY 関数です。引数にデータを入れると年月日を返してくれます。

DECLARE @datetime DATETIME = GETDATE();  
SELECT
    YEAR(@datetime)
    , MONTH(@datetime)
    , DAY(@datetime)

年月日の単純な取得であればこちらの関数のほうが可読性も高く良いと思います。

日時の差分を取得する(DATEDIFF)

DATEDIFF (Transact-SQL) – SQL Server | Microsoft Docs

DATEDIFF は、2つの日時データの差を求める関数です。結果はすべて整数値で返されます。　

DATEDIFF(要素, 開始日時, 終了日時)

開始日から終了日までの差分について、指定した要素で換算した差を求めてくれます。開始終了の日付が「開始 > 終了」になっていると結果はマイナスになります。

DECLARE @start DATETIME = '2019-11-10 12:34';  
DECLARE @end DATETIME = '2019-11-15 22:22'; 
SELECT 
    DATEDIFF(DAY, @start, @end)   -- 5
    , DATEDIFF(DAY, @end, @start) -- -5
    , DATEDIFF(HOUR, @start, @end) -- -130

上記結果から分かる通り、日数の計算なら時間以下、時間の計算なら分数以下の端数になるデータは切り捨てて計算されます。

以上。

参考URL

• 日付と時刻のデータ型および関数 (Transact-SQL) – SQL Server | Microsoft Docs
• DATEADD (Transact-SQL) – SQL Server | Microsoft Docs
• DATEPART (Transact-SQL) – SQL Server | Microsoft Docs
• DATEDIFF (Transact-SQL) – SQL Server | Microsoft Docs