SQLの間違い集1 Oracleで誕生日から満年齢を求める

OracleやMySQLで
よく見かけるSQLの間違いをまとめていきます。

Oracleで誕生日から満年齢を求める

テーブル定義とデータ準備

Create Table BirthDayTable(BirthDay date);
insert into BirthDayTable(BirthDay) values(date '2000-01-01');
insert into BirthDayTable(BirthDay) values(date '2008-02-28');
insert into BirthDayTable(BirthDay) values(date '2008-02-29');
insert into BirthDayTable(BirthDay) values(date '2008-03-01');

間違ったSQL

Months_Betweenで月の差を求めて、12で割って、小数を切り捨てる
といったもの

select to_char(date '2009-02-28','YYYY/MM/DD') as NowDate,
to_char(BirthDay,'YYYY/MM/DD') as BirthDay,
trunc(Months_Between(date '2009-02-28',BirthDay)/12) as Age
  from BirthDayTable
order by BirthDay;

NowDate     BirthDay    Age
----------  ----------  ---
2009/02/28  2000/01/01    9
2009/02/28  2008/02/28    1
2009/02/28  2008/02/29    1
2009/02/28  2008/03/01    0

間違いの理由

誕生日が2月29日の場合
うるう年でない年の2月28日の年齢が間違ってしまう。

正しいSQL

select to_char(date '2009-02-28','YYYY/MM/DD') as NowDate,
to_char(BirthDay,'YYYY/MM/DD') as BirthDay,
trunc((to_number(to_char(date '2009-02-28','yyyymmdd'))
      -to_number(to_char(BirthDay,'yyyymmdd')))/10000) as Age
  from BirthDayTable
order by BirthDay;

NowDate     BirthDay    Age
----------  ----------  ---
2009/02/28  2000/01/01    9
2009/02/28  2008/02/28    1
2009/02/28  2008/02/29    0
2009/02/28  2008/03/01    0