はじめに
最近、他の人が書いた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という風に計算します。
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を返します。
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した値を返します。
これによって、誕生日と今日の“月日”の大小を比較した結果から、誕生日と今日の“年”の差を比較し補正することができ、誕生日を正確に求めることができました。
まとめ
見落としがちな計算や考え方になるので、皆さん気をつけましょう！
レビュアーになっても見落とさないように！