8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 1 year has passed since last update.

SQLで年齢を計算するTips

Last updated at Posted at 2022-08-24

はじめに

最近、他の人が書いたSQL文をレビューしていて、年齢計算が間違っているのに気づきました。
一瞬合っているんじゃないかって思うんですよね。ただ、場合分けが必要になってくるので実はややこしいのがこの問題。

意外とめんどくさい文になってしまうなと感じたので、どんな文になったのか見ていきたいと思います。

最終的なゴール

この記事で、SQL Serverでの年齢計算の方法の1つを知ってもらえたらと思います。また、その考え方も知れると今後役に立つかもしれません!

間違っているSQL文

レビュー前のSQL文がこちらになります。

SELECT
    UserName as name 
    DATEDIFF(yyyy, Birthday, GETDATE()) AS age
FROM
    User

ぱっと見よさそうに見えますよね。ただ、こんなに簡単には計算できないのです。

各関数の意味

使われている関数の意味を見ていきましょう。

DATEDIFF

DATEDIFF関数は、2つの日付の差を計算する関数です。
DATEDIFF(interval, date1, date2)という風に引数を指定して利用します。
intervalにはyyyy(年)、m(月)、d(日)などの時間単位を指定します。
また、date2 - date1という風に計算します。

参考:https://support.microsoft.com/ja-jp/office/datediff-%E9%96%A2%E6%95%B0-e6dd7ee6-3d01-4531-905c-e24fc238f85f

GETDATE

GETDATE()関数は、今の時刻を取得する関数です。

参考:https://docs.microsoft.com/ja-jp/sql/t-sql/functions/getdate-transact-sql?view=sql-server-ver16

何が間違いなのか?

問題は、DATEDIFF関数が年を時間単位として計算していることです。
「年齢の計算だから年を時間単位にするはずだろう」という声が聞こえてきそうですが、注目すべきは時間単位が年であるとどんな問題が起こるかです!

次の表を見てみましょう。

誕生日 現在時刻 計算された年齢 実年齢
1999/1/1 2022/8/24 23 (2022-1999) 23
1999/12/31 2022/8/24 23 (2022-1999) 22

DATEDIFF関数が年を時間単位として計算しているため、どちらも2022 - 1999 = 23という計算から、年齢は23歳と計算されてしまいます。月日の要素が考えられていないのです。

正しい計算プロセス

正しい計算プロセスは次のようになります。

  1. 誕生日と今日の“月日”の大小を比較する。
  2. 誕生日と今日の“月日”の大小を比較した結果から、誕生日と今日の“年”の差を比較し補正する。

修正後

修正後のSQL文がこちらになります。

SELECT
    UserName as name 
    , IIF(
        DATEADD(yyyy, DATEDIFF(yyyy, Birthday, GETDATE()), Birthday) <= GETDATE()
        , DATEDIFF(yyyy, Birthday, GETDATE())
        , DATEDIFF(yyyy, Birthday, GETDATE()) - 1
    ) AS age
FROM 
    User

1つずつ見ていきましょう。

各関数の意味

使われている関数の意味を見ていきましょう。

IIF

IIF関数は、if文と同じで、ある論理式の結果によって挙動を変える関数です。
IIf ( expr , truepart , falsepart )という風に書きます。
exprが論理式を表し、その結果がtrueならtruepartを、falseならfalsepartを返します。

参考:https://support.microsoft.com/ja-jp/office/iif-%E9%96%A2%E6%95%B0-32436ecf-c629-48a3-9900-647539c764e3

DATEADD

DATEADD関数は、指定した時間単位で日付を加算する関数です。
DATEADD(interval, number, date)という風に書きます。
intervalは時間単位の指定、numberが追加する数、dateが追加される日付を表します。

参考:https://docs.microsoft.com/ja-jp/office/vba/language/reference/user-interface-help/dateadd-function

プロセス1

プロセス1は、「誕生日と今日の“月日”の大小を比較する」です。
このプロセスは、IIF関数の第一引数で行なっています。

こちらの表の値を例に説明していきます。

誕生日 現在時刻 計算された年齢 実年齢
1999/1/1 2022/8/24 23 (2022-1999) 23

まず、DATEDIFF(yyyy, Birthday, GETDATE())誕生日と今日の”年”の差を求めます。
例で言うと、2022 - 1999 = 23という値が返されます。

次に、DATEADD(yyyy, DATEDIFF(yyyy, Birthday, GETDATE()), Birthday)で誕生日の”年”に誕生日と今日の年の差を足します。
例で言うと、1999 + 23 = 2022より、2022/1/1という値が返されます。

最後に、DATEADD(yyyy, DATEDIFF(yyyy, Birthday, GETDATE()), Birthday) <= GETDATE()で、誕生日と今日の“月日”の大小を比較します。
例で言うと、2022/1/12022/8/24を比較します。

これによって、誕生日と今日の“月日”の大小を比較することができました。

プロセス2

プロセス2は、「誕生日と今日の“月日”の大小を比較した結果から、誕生日と今日の“年”の差を比較し補正する」です。
このプロセスは、IIF関数の第二、第三引数で行なっています。

プロセス1より、誕生日の月日 <= 今日の月日であればtrueとなり第二引数を、誕生日の月日 > 今日の月日であればfalseとなり第三引数の値を返します。

誕生日の月日 > 今日の月日の場合は、今年まだ誕生日が来ていないということなので、-1した値を返します。

これによって、誕生日と今日の“月日”の大小を比較した結果から、誕生日と今日の“年”の差を比較し補正することができ、誕生日を正確に求めることができました。

まとめ

見落としがちな計算や考え方になるので、皆さん気をつけましょう!
レビュアーになっても見落とさないように!

8
6
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8
6

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?