Calculate Age (Years, Months, Days) :Firebird database

ไม่อยากใช้ Procedure อยากจะเขียน SQL เพรียว ๆ ผลก็อย่างที่เห็น

ยาวไปมั้ยหรือว่ามันง่ายกว่านี้ได้อีก แต่คิดออกแค่นี้ DB.Firebird 2.5

Table Students

create table students (

 std_code varchar(50) not null,

 std_name varchar(200) ,

 dob date

);

SELECT
       Rep.Std_Code,
       Rep.Std_Name,
       Rep.DOB,
       Rep.Years,
       DATEDIFF(MONTH, Rep.date2CalMonth, CURRENT_DATE) Months,
       DATEDIFF(DAY, Rep.date2CalDay, CURRENT_DATE) Days
FROM
(
select
   std.Std_code,
   std.Std_Name,
   std.dob,
     (datediff(year, std.dob, current_date)-
     (case when dateadd(year, datediff(year, std.dob, current_date), std.dob)>current_date then 1 else 0 end)
     ) Years,

     dateadd(year, (datediff(year, std.dob, current_date)-
      (case when dateadd(year, datediff(year, std.dob, current_date), std.dob)>current_date then 1 else 0 end)
     ), std.dob)
     date2CalMonth,

     case when extract(month from std.dob) = extract(month from current_date) and extract(day from std.dob) = extract(day from current_date)
          then dateadd(year, datediff(year, std.dob, current_date), std.dob)
          else
             dateadd(month,
                         ((-1)*(datediff(month,
                                       current_date,
                                       (dateadd(year, datediff(year, std.dob, current_date), std.dob))
                                      )+case when extract(day from std.dob)>=extract(day from current_date) then 1 else 0 end
                              )),
                    (dateadd(year, datediff(year, std.dob, current_date), std.dob))
             )
     end date2CalDay
from students std order by std.Std_code asc
) rep

======= How to =======

Years = YEAR* - X*

*remark: YEAR* = Year_Diff(dob, current_date)

               X*    = when dob + YEAR* > current_date then 1 else 0 end
---------------------

date2CalMonth = dob+YEAR* - X*

*remark: YEAR* = Year_Diff(dob, current_date)

               X*    = when dob + YEAR* > current_date then 1 else 0 end

---------------------

date2CalDay = when getMonth(dob) = getMonth(current_date) and

                   getDay(dob) >= getDay(current_date)

      then result = dob+YEAR*

      else result = newdob* + (-1)*MONTH* + X*

*remark: 

             YEAR* = Year_Diff(dob, current_Date)

           newdob = dob + Year_Diff(dob, current_date)

         MONTH* = Month_Diff(current_date, (dbo+YEAR*)

              X*     =  when getDay(dob) >= getDay(current_date) then 1 else 0 end

---------------------

SQL SERVER
MYSQL

Internal function.
CURRENT_DATE
EXTRACT
DATEDIFF
DATEADD

update 13/06/2017
Fixed bug