Calculating Age from Birthday - Tsql, Oracle, and Any Others

Calculating Age from Birthday - TSQL, Oracle, and any others

A correct answer

Cobbled together from the sources given, this is the correct answer for SQL Server (from 2012):

IIF(reference_date < date_of_birth, -1, CAST(CONVERT(CHAR(8), reference_date, 112) AS INT) - CAST(CONVERT(CHAR(8), date_of_birth, 112) AS INT) / 10000)

This converts a date into an ISO date string (YYYYMMDD), casts the string to and int, and then subtracts one from the other - if the month and monthday of birth are higher than the reference date, this subtraction borrows a digit from the year component. We then integer-divide by 10,000 to produce the whole-years difference. This works seamlessly across leap years. Time parts are automatically truncated in the conversion. CHAR(8) is used as the string is fixed length.

For other platforms, I would recommend this SQL Server solution be adapted. For SQL Server pre-2012, replace the IIF with a CASE.

There are of course a variety of other correct ways, but this appears to be the inline solution that is most economical with syntax, and I have not seen any better.

---

Incorrect answers

I just thought I'd take a minute to address incorrect answers in various sources, for the benefit of any future readers.

1. DATEDIFF(YEAR, date_of_birth, reference_date)

As mentioned in my question, this seems to be the first approach many try. Unfortunately it only counts the difference in the year-part of the date, so that a baby born 31-Dec-17 turns 1 years old on 01-Jan-18. It's good but it's not right.

2. FLOOR(DATEDIFF(DAY, date_of_birth, reference_date) / 365.25)

This seems to be another common approach - and was also given in a comment on my question.

For a baby born 28-Feb-17, properly calculated they turn 1 years old on 28-Feb-18 - their next birthday. 365 days elapse between the two dates. 365 / 365.25 = 0.99. Floored it becomes zero. The baby is treated as turning 1 on 1-Mar-18 - a day too late.

Further refining the "days per year" divisor after the decimal point does not help, as any divisor above 365 exactly, will fail in this case. It's good but it's not right.

3. DATEDIFF(HOUR, date_of_birth, reference_date) / 8766

A variation on (2) is to use hours instead of days. There is a fixed number of hours in every day, therefore 8766/24 = 365.25. We see this solution is equivalent to (2). It's good but it's not right.

4. CONVERT( INT, ROUND(DATEDIFF(HOUR, date_of_birth, reference_date) / 8766.0, 0) )

Another variation is to use rounding on the hours. This suffers from exactly the same defect as (3) for the same reasons, but additionally if a time component is involved, then it rounds up the last 30 minutes of the day prior to the birthday. It's good but it's not right.

5. DATEPART(DAYOFYEAR, ...)

Others have explored whether DAYOFYEAR can provide a solution. Unfortunately, due to leap years, the standard DAYOFYEAR does not map consistently to specific dates (months and monthdays) of the year. It's good but it's not right.

---

A maybe answer

6. FLOOR( MONTHS_BETWEEN(reference_date, date_of_birth) / 12 )

This Oracle-specific solution falls down when the date of birth is 29-Feb (in a leap year), and the reference date is 28-Feb (in a non-leap year), in that it treats the birthday as 28-Feb in non-leap years.

That actually may be closer to the common convention on when a birthday is celebrated for leap-year babies, but I've specified in my question that in such cases the birthday for the purposes of calculating a person's age should be treated as 1-Mar (which is consistent with UK law).

The MONTHS_BETWEEN also has other quirks which might be undesirable for age calculations (for example, if it is used to calculate age as a number of months, such as for babies less than 1 years old, where the "birthday" falls on or after the same monthday in subsequent months).

It's good and it may be considered right in some contexts!

---

Summary

There are of course other ways to implement a correct solution than the one given here, but heed should be taken of the litany of incorrect answers (and many more overwrought examples that I do not consider here), and any innovation or alternative calculation should be thoroughly tested and compared against one that is already known to work.

Oracle Age calculation from Date of birth and Today

SQL> select trunc(months_between(sysdate,dob)/12) year,
  2         trunc(mod(months_between(sysdate,dob),12)) month,
  3         trunc(sysdate-add_months(dob,trunc(months_between(sysdate,dob)/12)*12+trunc(mod(months_between(sysdate,dob),12)))) day
  4  from (Select to_date('15122000','DDMMYYYY') dob from dual);

      YEAR      MONTH        DAY
---------- ---------- ----------
         9          5         26

SQL>

Oracle PL/SQL, How to calculate age from date birth using simple substitution variable and months_between

All SQL examples from others and me are probably better thing to use unless you are learning something about PL/SQL. Here's what I came up with:

 DECLARE
  v_dob        DATE:= to_date('&v_dob', 'MM/DD/YYYY'); -- copy this line and you'll be fine
  v_your_age   NUMBER(3, 1);
BEGIN
  v_your_age := TRUNC(MONTHS_BETWEEN(SYSDATE, v_dob))/12;  
  DBMS_OUTPUT.PUT_LINE ('Your age is ' || v_your_age);
END;
/

I passed 01/01/2010. Got this in output: 

 Your age is 3.1

Another SQL example:

SELECT empno, ename, hiredate
    , TRUNC(MONTHS_BETWEEN(sysdate, hiredate)/12) years_of_service  
 FROM scott.emp
/

Oracle query to calculate current age

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.

Calculating age from birthday with oracle plsql trigger and insert the age in table

please help...i really need this...

No, you don't. I'm not sure you'll pay attention; and there's no reason why you should :-) but:

Do not store age in your database. You are absolutely guaranteed to be wrong occasionally. Age changes each year for each person, however, it changes every day for some people. This in turn means you need a batch job to run every day and update age. If this fails, or isn't extremely strict and gets run twice, you're in trouble.

You should always calculate the age when you need it. It's a fairly simple query and saves you a lot of pain in the longer run.

select floor(months_between(sysdate,<dob>)/12) from dual

I've set up a little SQL Fiddle to demonstrate

---

Now, to actually answer your question

this procedure works fine but for only one row,,,but for all the rows
i need trigger but if i call it from a trigger then the error
occurs...

You don't mention the error, please do this in future as it's very helpful, but I suspect you're getting

ORA-04091: table string.string is mutating, trigger/function may not
see it

This is because your procedure is querying the table that is being updated. Oracle does not allow this in order to maintain a read-consistent view of the data. The way to avoid this is to not query the table, which you don't need to do. Change your procedure to a function that returns the correct result given a date of birth:

function get_age (pDOB date) return number is
   /* Return the the number of full years between 
      the date given and sysdate.
      */    
begin    
   return floor(months_between(sysdate,pDOB)/12);    
end;

Notice once again that I'm using the months_between() function as not all years have 365 days.

In your trigger you then assign the value directly to the column.

CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
   :new.age := get_age(:new.dob);
END;

The :new.<column> syntax is a reference to the <column> that is being updated. In this case :new.age is the actual value that is going to be put in the table.

This means that your table will automatically be updated, which is the point of a DML trigger.

As you can see there's little point to the function at all; your trigger can become

CREATE OR REPLACE TRIGGER agec before INSERT OR UPDATE ON dates
FOR EACH ROW
BEGIN
   :new.age := floor(months_between(sysdate,:new,DOB)/12);
END; 

However, having said that, if you are going to use this function elsewhere in the database then keep it separate. It's good practice to keep code that is used in multiple places in a function like this so it is always used in the same way. It also ensures that whenever anyone calculates age they'll do it properly.

As a little aside are you sure you want to allow people to be 9,999 years old? Or 0.000000000001998 (proof)? Numeric precision is based on the number of significant digits; this (according to Oracle) is non-zero numbers only. You can easily be caught out by this. The point of a database is to restrict the possible input values to only those that are valid. I'd seriously consider declaring your age column as number(3,0) to ensure that only "possible" values are included.

PL/SQL: How can I calculate the AGE knowing a column called Birth_Date?

To get a person's age according to the usual criterion (i.e. according to the number of calendar years that have passed since their birth), taking into account leap years, you can use the MONTHS_BETWEEN operator:

SELECT id, MONTHS_BETWEEN(sysdate, birth_date) / 12 age FROM my_table;

How to calculate age (in years) based on Date of Birth and getDate()

There are issues with leap year/days and the following method, see the update below:

try this:

DECLARE @dob  datetime
SET @dob='1992-01-09 00:00:00'

SELECT DATEDIFF(hour,@dob,GETDATE())/8766.0 AS AgeYearsDecimal
    ,CONVERT(int,ROUND(DATEDIFF(hour,@dob,GETDATE())/8766.0,0)) AS AgeYearsIntRound
    ,DATEDIFF(hour,@dob,GETDATE())/8766 AS AgeYearsIntTrunc

OUTPUT:

AgeYearsDecimal                         AgeYearsIntRound AgeYearsIntTrunc
--------------------------------------- ---------------- ----------------
17.767054                               18               17

(1 row(s) affected)

UPDATE here are some more accurate methods:

BEST METHOD FOR YEARS IN INT

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-05-04', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1989-05-06', @Dob='1980-05-05'  --results in  9
--SELECT @Now='1990-05-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1990-12-06', @Dob='1980-05-05'  --results in 10
--SELECT @Now='1991-05-04', @Dob='1980-05-05'  --results in 10

SELECT
    (CONVERT(int,CONVERT(char(8),@Now,112))-CONVERT(char(8),@Dob,112))/10000 AS AgeIntYears

you can change the above 10000 to 10000.0 and get decimals, but it will not be as accurate as the method below.

BEST METHOD FOR YEARS IN DECIMAL

DECLARE @Now  datetime, @Dob datetime
SELECT   @Now='1990-05-05', @Dob='1980-05-05' --results in 10.000000000000
--SELECT @Now='1990-05-04', @Dob='1980-05-05' --results in  9.997260273973
--SELECT @Now='1989-05-06', @Dob='1980-05-05' --results in  9.002739726027
--SELECT @Now='1990-05-06', @Dob='1980-05-05' --results in 10.002739726027
--SELECT @Now='1990-12-06', @Dob='1980-05-05' --results in 10.589041095890
--SELECT @Now='1991-05-04', @Dob='1980-05-05' --results in 10.997260273973

SELECT 1.0* DateDiff(yy,@Dob,@Now) 
    +CASE 
         WHEN @Now >= DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)) THEN  --birthday has happened for the @now year, so add some portion onto the year difference
           (  1.0   --force automatic conversions from int to decimal
              * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
              / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
           )
         ELSE  --birthday has not been reached for the last year, so remove some portion of the year difference
           -1 --remove this fractional difference onto the age
           * (  -1.0   --force automatic conversions from int to decimal
                * DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),DATEPART(m,@Dob),DATEPART(d,@Dob)),@Now) --number of days difference between the @Now year birthday and the @Now day
                / DATEDIFF(day,DATEFROMPARTS(DATEPART(yyyy,@Now),1,1),DATEFROMPARTS(DATEPART(yyyy,@Now)+1,1,1)) --number of days in the @Now year
             )
     END AS AgeYearsDecimal

---

Related Topics