Convert Date of Birth to Age, results a negative number
Convert Date of Birth to Age, results a negative number
(OP)
Greetings,
I'm able to convert a birthdate to age and all is fine except for years in the early 1900s, 1912 - 1920 for instance. I need to correct this, what do I need to add to the query string?
All help is greatly appreciated!
Thanks,
JustATheory
I'm able to convert a birthdate to age and all is fine except for years in the early 1900s, 1912 - 1920 for instance. I need to correct this, what do I need to add to the query string?
All help is greatly appreciated!
Thanks,
JustATheory
Talk To Other Members
RE: Convert Date of Birth to Age, results a negative number
What is your current query string?
What RDBMS are you using.
RE: Convert Date of Birth to Age, results a negative number
Oracle 11g
SELECT ID, DOB, floor((months_between(sysdate, DOB))/12) age FROM table
;
Sample Results
ID DOB AGE
1 25-Aug-54 57
2 10-Feb-85 27
3 20-Sep-80 31
4 10-Aug-18 -7
Many Thanks!
JustATheory
RE: Convert Date of Birth to Age, results a negative number
RE: Convert Date of Birth to Age, results a negative number
But the short answer is you are using an RR date model for years. In essence, Oracle is rounding off your century, and years below 50 (or is it 51? I forget) are assumed to be in the current century. So 10-Aug-18 is considered to be 2018, not 1918.
Try this:
CODE
SELECT TO_CHAR(dob, 'DD-MON-YYYY') AS real_date FROM table;
RE: Convert Date of Birth to Age, results a negative number
JustATheory