Home » SQL & PL/SQL » SQL & PL/SQL » to calculate 65 years from Date of birth
to calculate 65 years from Date of birth [message #33065] Fri, 17 September 2004 11:16 Go to next message
Anju
Messages: 33
Registered: September 2000
Member
Hi,
I have a DOB column in a table. I want to calculate WHEN AND WHICH YEAR FROM THEIR DOB, EACH PERSON WILL BECOM 65 YEARS...

I am using formula select (ins_dob + (65 * 365.25)) sixtyfive from ins_table.

this does not return proper dates for some records...

Please help Me :) :)

Regards
anju
Re: to calculate 65 years from Date of birth [message #33066 is a reply to message #33065] Fri, 17 September 2004 11:51 Go to previous messageGo to next message
shoblock
Messages: 325
Registered: April 2004
Senior Member
It's because of leap years, and 65 is not a multiple of four, so it misses every so often - if you're born on Mar 3, 2000 (a leap year), your first birthday is not Mar 2, 2001. Likewise, 365 days from Feb 2, 2000 is Feb 1, 2001 - again, not the desired answer.

you could use: add_months(ins_dob, 65*12)
this will only be an issue if someone was born Feb 28, and their 65 birthday occurs in a leap year. then the result will be a Feb 29 date.

any complex date routine (substr out the year, add 65, concat it back) will have problems if the person was born on a Feb 29.
Re: to calculate 65 years from Date of birth [message #33088 is a reply to message #33066] Mon, 20 September 2004 04:00 Go to previous message
Anju
Messages: 33
Registered: September 2000
Member
Thanks Shoblock... i was trying to use add_months and quit it because of weekend... let me try it today... thanks for the help..

God Bless

anju
Previous Topic: nth row in a table
Next Topic: Move a standalone function into a Package
Goto Forum:
  


Current Time: Wed Jul 30 14:14:48 CDT 2025