How to calculate age in years in SQL

Question

Need to create a basic Age Calculator function which calculates the age in years on the age field of the peoples table.

The function should be called agecalculator, it needs to take 1 date and calculate the age in years according to the date NOW and must return an integer.

You may query the people table while testing but the query must only contain the function on your final submit.

people table schema

id
name
age

This is my code:

select convert(int,DATEDIFF(d, '1933-10-31', getdate())/365.25)

You told us your assignment. You posted a line of code. Now, what's the problem? — takendarkk, Jan 17 '17 at 22:08
Possible duplicate of [How to calculate age (in years) based on Date of Birth and getDate()](http://stackoverflow.com/questions/1572110/how-to-calculate-age-in-years-based-on-date-of-birth-and-getdate) — Rion Williams, Jan 17 '17 at 22:09
I am using oracle and the problem is it doesnt calculate the age — Majed Mahmood, Jan 17 '17 at 22:10
There is no `datediff` `convert()` or `getdate()` in Oracle. Where in the [Oracle manual](https://docs.oracle.com/database/121/SQLRF/toc.htm) did you find that? — , Jan 17 '17 at 22:14
Well, how do you calculate an age in general (outside of SQL)? Subtract the current year from the birth year. If the day of the year is less that the birth day of year, then subtract one from that (since the birthday for this year hasn't been reached yet). Now just code that in SQL :) You can get _close_ with what you have but it may be off by one if the day of the year is within one day the birthday. — D Stanley, Jan 17 '17 at 22:20

score 0 · Answer 1 · answered Jan 18 '17 at 09:20

Try this:

 DECLARE @DOB DATETIME ='12/29/1980'

 SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',

 DATEDIFF(YEAR,@DOB,GETDATE()) -
 (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB) > GETDATE() 
    THEN 1
    ELSE 0 
      END) 'Age in Years'

score 0 · Answer 2 · answered Feb 05 '19 at 21:16

You need to create a function,so use the CREATE FUNCTION statement .

Try this:

CREATE FUNCTION ageCalculator (a timestamp) RETURNS integer AS $$
BEGIN
  RETURN (SELECT date_part('year',age(a)))::int;
END;
 $$ LANGUAGE plpgsql;

Or:

CREATE FUNCTION ageCalculator (a timestamp) 
RETURNS integer AS $$
BEGIN
  RETURN (SELECT EXTRACT(year FROM age(a)))::int;
END;
 $$ LANGUAGE plpgsql;

score -1 · Answer 3 · edited Aug 05 '19 at 07:43

-1

DECLARE @DOB DATETIME ='08/14/1980'

 SELECT @DOB 'Date of Birth', GETDATE() 'Current Date',

 DATEDIFF(YEAR,@DOB,GETDATE()) -
 (CASE WHEN DATEADD(YY,DATEDIFF(YEAR,@DOB,GETDATE()),@DOB) > GETDATE() 
    THEN 1
    ELSE 0 
      END) 'Age in Years'

edited Aug 05 '19 at 07:43

DxTx

3,049
3
23
34

answered Aug 05 '19 at 06:32

M.santhosk kumar

1

2

hi! can you add some explanation to the code you wrote inside your answer? – Giulio Caccin Aug 05 '19 at 07:01

How to calculate age in years in SQL

3 Answers3