Convert Date of Birth to Age, results a negative number

[JustATheory]

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

 

[carp]

We're missing at least one important piece of information.
What is your current query string?
What RDBMS are you using.

 

[JustATheory]

Sorry about that!

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

 

[JarlH]

The Oracle date/time handling isn't ANSI compliant, so I suppose you'd better ask the Oracle people,

http://www.tek-tips.com/threadminder.cfm?pid=1177

 

[carp]

Jarl is correct - this is an Oracle question.
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:

SELECT TO_CHAR(dob, 'DD-MON-YYYY') AS real_date FROM table;

and the results of your original query will be clear.

 

[JustATheory]

Thanks to both Jarl and carp, this works perfectly and I'll check out the oracle 11g forum.

JustATheory