Oracle query to calculate current age

Question

I want to calculate current age of person from DOB(date of birth) field in Oracle table.

Data type of DOB field is varchar and the is date stored in format 'DD-MON-YY'.

when I calculate current age of a person from date like 10-JAN-49 the query will return age in negative. Also, I observed that if date has year 13 to 49 it gives negative result.

Examples

22-NOV-83 -valid result
09-FEB-58 --valid result
05-JUN-49 - Invalid result like -36

Query Executed for reference

select round(MONTHS_BETWEEN(sysdate,to_date(dob,'DD-MON-RR'))/12)||' Yrs' 
from birth

Any help is appreciated!

+1 for Ben -- if a date is stored as 10-JAN-12, is the person currently 1 year or 101 years old? You need to get that changed. — David Aldridge, Jul 02 '13 at 11:21

score 5 · Answer 1 · answered Jul 02 '13 at 09:53

5

/*
A value between 0-49 will return a 20xx year.
A value between 50-99 will return a 19xx year.
*/

Source: http://www.techonthenet.com/oracle/functions/to_date.php

SELECT  FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(d_date, 1, 7) || '19' || SUBSTR(d_date, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
FROM
(
        SELECT  '10-JAN-49' d_date FROM DUAL
)

-- The result: 64

answered Jul 02 '13 at 09:53

the_slk

2,172
1
11
10

Will only work for people born in the 20th century, i.e. not for a 12 year old, which I guess will suffice for now, but maybe not in 5 years time? – Ronnie Jul 02 '13 at 09:56
I know. I would rewrite this table to a new structure and introduced a view. Maybe there is some better solution for that... – the_slk Jul 02 '13 at 10:03
@Ronnie how do we solve this 12yrs old issue...as I need to run update query to update age column for 60000000 records – user1760986 Jul 02 '13 at 10:31
@Ronnie how do I solve scenario for 12 yr old..I want to run query on 60000000 rows..for other dates in 19 century its working perffect but problem for 12 yrs old it gives 103 yrs current age like wise – user1760986 Jul 02 '13 at 10:39
@user1760986: You have to determine what is the oldest user in you table. If it is 70 years old then this is the end of you lookup. 2013 - 70 = 1943. Now you know that for "YY" from 43 to 99 you have to use '19' in other case you have to use '20' to get a correct date value. Add it as another CASE. – the_slk Jul 02 '13 at 20:09

score 5 · Accepted Answer · answered Jul 02 '13 at 10:46

To get round the 21st century problem, just modifying @the_silk's answer slightly:

SELECT
  CASE WHEN SUBSTR(dob, -2, 2) > 13
  THEN FLOOR
        (
            MONTHS_BETWEEN
            (
                SYSDATE
            ,   TO_DATE(SUBSTR(dob, 1, 7) || '19' || SUBSTR(dob, -2, 2), 'DD-MON-YYYY')
            ) / 12
        )
  ELSE
       FLOOR(MONTHS_BETWEEN(sysdate,TO_DATE(dob,'DD-MON-YY'))/12)
  END
FROM
birth

Please be aware though that this assumes that any date year between '00' and '13' is 21st century, so this sql should only be used if you are building a one off throwaway script, otherwise it will become out of date and invalid before long.

The best solution would be to rebuild this table, converting the varchar column into a date column, as alluded to by Ben.

Yes agree to convert column to date..as data is massive so it will take much time...you updated query worked like charm except the scenario of 19 century...thank you very much.. — user1760986, Jul 02 '13 at 11:06

score 1 · Answer 3 · edited Sep 18 '13 at 20:53

1

SELECT MONTHS_BETWEEN( to_date(sysdate,'dd-mm-rr')
                     , to_date(to_date(dob,'yymmdd'),'dd-mm-rr')
                     ) / 12 AS Years 
 FROM birth;

Maybe this works.

edited Sep 18 '13 at 20:53

Ben

51,770
36
127
149

answered Sep 18 '13 at 20:34

user2793088

11
1

You're converting dates to dates here... so you're going to get errors. – Ben Sep 18 '13 at 20:53
I have similar select statement where I'm converting dates to dates and when I run it in my sql*plus command-line I get no errors. Works fine for me. E: you're probably right. Looked it up and mine statement has **substr** instead of **dob**. – user2793088 Sep 18 '13 at 21:22
Incorrect things can seem to work fine for a limited subset of inputs. You're doing multiple implicit conversions which means it's easy for things to go wrong, [for instance look at the year here](http://www.sqlfiddle.com/#!4/d41d8/17451). It comes with a performance impact and [Oracle explicitly recommends against implicit conversion](http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements002.htm#i53062). Why not do things correctly first time around and then you _know_ everything will be okay? – Ben Sep 18 '13 at 21:29
Okey. Thanks for explaining it, Ben. – user2793088 Sep 19 '13 at 13:38

score 1 · Answer 4 · edited Dec 20 '14 at 08:37

1

My solution would be something like this:

SELECT DATE_FORMAT(CURDATE(),'%Y') - DATE_FORMAT(dob,'%Y') as age
FROM `member_master`
having age >=30 and age <=35

edited Dec 20 '14 at 08:37

Sebastiano

12,289
6
47
80

answered Dec 20 '14 at 08:10

Pankaj

11
1

score 0 · Answer 5 · edited Oct 27 '16 at 12:38

SELECT year, 
       months, 
       Floor(SYSDATE - day_1) AS days 
FROM   (SELECT year, 
               months, 
               Add_months(To_date('30-Oct-1980', 'dd-Mon-yyyy'), 
               12 * year + months) 
                      day_1 
        FROM   (SELECT year, 
                       Floor(Trunc(Months_between(Trunc(SYSDATE), 
                                   To_date('30-Oct-1980', 'dd-Mon-yyyy') 
                                   )) 
                             - year * 12) AS months 
                FROM   (SELECT Floor(Trunc(Months_between(Trunc(SYSDATE), 
                                                   To_date('30-Oct-1980', 
                                                   'dd-Mon-yyyy' 
                                                   ) 
                                                           )) / 
                                                     12) AS year 
                        FROM   dual)));

Edit your answer to format the query as it's a little unreadable. Have a look at [this](http://stackoverflow.com/help/formatting). — Bugs, Oct 27 '16 at 12:37

score -1 · Answer 6 · answered Jul 02 '13 at 09:54

-1

Try using YY in the Year part of your TO_DATE

RR Like YY, but the two digits are ``rounded'' to a year in the range 1950 to 2049. Thus, 06 is considered 2006 instead of 1906

answered Jul 02 '13 at 09:54

Tamim Al Manaseer

3,554
3
24
33

This would not work. In fact it would lead to all the example dates being interpreted as a 21st century date. – Ronnie Jul 02 '13 at 10:07
The years in your examples are all above "49" so they will be considered 19**, if you agree please remove your downvote – Tamim Al Manaseer Jul 02 '13 at 10:10
Sorry I don't agree. You have suggested using YY. The years above 49 are considered 19** only if you use RR. So please remove YOUR down vote! I will remove mine if you modify your answer to be correct. For example, try this: select to_char(to_date('22-NOV-83','DD-MON-YY'),'YYYY') FROM DUAL – Ronnie Jul 02 '13 at 10:12
1

Sorry, just tried it on SQL Fiddler, your correct :) Keep the down vote for people to learn – Tamim Al Manaseer Jul 02 '13 at 10:22

Oracle query to calculate current age

6 Answers6

Linked