SQLマスターの皆さまこんにちは。
今日は、ちょっとした日付変換のテクニックを紹介したいと思います。
会計年度の計算って皆さんどうされていますか？
残念ながらSQLの関数には一発で取れるものがないので、日付型の値にMONTH()を使って3月なら～等と計算式を書かれてますよね。
ですが今回のテクニックを使えばワンライナーで、簡単に会計年度を計算できますよ。
問題の背景
通常、1月1日から12月31日までを1年とするのが「リアルイヤー（実際の年）」ですよね。
でもビジネスの世界では、4月1日から翌年の3月31日までを1年とする「ファイナンシャルイヤー（会計年度）」という考え方がよく使われます。
こういった場合、日付データを変換する必要が出てくるわけです。
業務システムなんかをつくていると、このような変換が必要になるケースは結構あるんじゃないでしょうか。
もしリアルイヤーが2024年でも、まだ３月だったなら会計年度は2023年になるので -1 しないといけないのですが、
FY(sysdate)みたいな関数は現時点では存在しません。
ワンライナーで解決
さて、お待たせしました。以下がその神秘的な一行です。なお、これは MySQL の例です。
YEAR(DATE_ADD(real_date, interval 275 day)) - 1;SQLに詳しい皆さんは、この一行で何が行われているのかパッと見で理解できるかもしれませんが、説明を加えましょう。
- DATE_ADD()関数:
real_dateという日付に275日を加算します。 - YEAR()関数: 加算した日付の年を取得します。
- – 1: その年から1を引きます。
なぜこれで動くの？
このコードのキモは、interval 275 day の部分です。
4月1日から275日後は、次の年の1月1日になります。
だから、4月1日（またはそれ以降）の日付に275日を足すと、次の「リアルイヤー」に跳びます。
そして、その年から1を引くことで「ファイナンシャルイヤー」に戻るわけです。
うるう年は？
もちろんうるう年も対応しています。
なぜなら、うるう年かどうかで変わるのは１月から３月までの日数です。
4月1日から12月31日までの日数は、うるう年も、そうでない年も変わらず “275日” なんですね。
例で見てみよう
入力が 2022-04-15（4月15日）の場合:
- 275日を加えると
2023-01-15 - 年は
2023 - 1を引いて
2022となり、これがファイナンシャルイヤー
簡単ですよね！一行で済ませられるのが、SQLの美しいところです。
ストアド ファンクションでは
ストアド関数ならこんな感じです。こちらも MySQL の例です。
DELIMITER //
CREATE FUNCTION get_f_year(real_date DATE) RETURNS INT DETERMINISTIC
BEGIN
RETURN YEAR(DATE_ADD(real_date, interval 275 day)) - 1;
END;
//
DELIMITER ;
まとめ
日付の変換は一見難しそうに見えるかもしれませんが、このようなスマートな方法も存在します。
次回このような問題に遭遇したら、この一行を思い出してください。
それでは皆様、よいクエリ生活を！
コメント