はじめに

最近、他の人が書いた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歳と計算されてしまいます。月日の要素が考えられていないのです。

正しい計算プロセス

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

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

修正後

修正後の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/1と2022/8/24を比較します。

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

プロセス2

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

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

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

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

まとめ

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