Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...I'm so glad I found this site... Now I can get some sleep, because my problem is solved..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > Languages > ANSI_SQL Forum

Convert Date of Birth to Age, results a negative number

thread220-1675285
Read More Threads Like This One
JustATheory (IS/IT--Management)
16 Feb 12 16:43
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 (MIS)
16 Feb 12 19:08
We're missing at least one important piece of information.
What is your current query string?
What RDBMS are you using.
JustATheory (IS/IT--Management)
16 Feb 12 19:19
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 (Programmer)
17 Feb 12 2:33
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 (MIS)
17 Feb 12 8:47
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:

CODE

  
SELECT TO_CHAR(dob, 'DD-MON-YYYY') AS real_date FROM table;
and the results of your original query will be clear.
JustATheory (IS/IT--Management)
17 Feb 12 13:18
Thanks to both Jarl and carp,  this works perfectly and I'll check out the oracle 11g forum.

JustATheory
Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To ANSI_SQL

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close