How to calculate age in SQL

Question

When attempting to calculate age of teacher I'm receiving an error 'CURDATE' is not a recognized built-in function name., I'm new to SQL and learning on my own so i can't quite figure out where I'm going wrong with this query. any hints would be appreciated.

SAMPLE DATA HERE

SELECT t.TeacherID, t.TeacherFName, t.TeacherLName,DOB
FROM TEACHER t INNER JOIN CLASS c
ON t.TeacherID = c.TeacherID 
WHERE TIMESTAMPDIFF(YEAR, DOB, CURDATE())>=60;

-Question example

A program coordinator is interested in knowing the age of their teachers who teach all classes, and to find this info he needs the teacherID, teachers first and last name

-Table below

CREATE TABLE TEACHER (  
teacherNo int primary key,
teacherFName varchar (25) NOT NULL,
teacherLName varchar (25) NOT NULL,
teacherPhone nvarchar (10),
DOB  datetime,
Salary money
)

CREATE TABLE CLASS (
classNo int primary key,
classDay date,
teacherID int,
NoOfStudents int,
)

Adding sample data would help get the point of your question across. — Tim Biegeleisen, May 23 '21 at 05:58
Also add what database are you usingSome Sql commands are no commons to different databases, for example: MySQL uses CURDATE(), PostgresSQL CURRENT_DATE(), SQLLite DATE(‘now’) and SQL Server GETDATE() Source: https://www.datacamp.com/community/blog/sql-differences — Gonzalo Odiard, May 23 '21 at 06:10
Date of Birth (DOB) is NOT a datetime! It is a date and only a date. — SMor, May 23 '21 at 11:44
In your query you use `TeacherID`, but that is not an existing field in the table `TEACHER`. — Luuk, May 23 '21 at 13:03
In your query you use `TIMESTAMPDIF`, but that is not existing function in sql-server. — Luuk, May 23 '21 at 13:04

score 0 · Answer 1 · answered May 23 '21 at 06:00

0

In SQL Server you should use DATEDIFF() function.

SELECT t.TeacherID, t.TeacherFName, t.TeacherLName, 
DATEDIFF(year, t.DOB, getdate()) AS Age
FROM TEACHER t INNER JOIN CLASS c
ON t.TeacherID = c.TeacherID

answered May 23 '21 at 06:00

parsa2820

570
4
13

This won't produce an accurate answer 100% of the time – Tanner May 23 '21 at 06:12
@Tanner What do you mean? – parsa2820 May 23 '21 at 11:15
If I was born on the 31st December 2021 and today's date was 1st January 2022 your query would say I was 1 year old rather than 1 day – Tanner May 20 '22 at 09:48

score 0 · Accepted Answer · answered May 23 '21 at 12:05

The first problem with your query is that TIMESTAMPDIFF() is a MySQL function, not a SQL Server function. And so is CURDATE(). It is tempting to replace it with DATEDIFF(), but they do different things.

The correct logic for what you want is:

WHERE DOB < DATEADD(YEAR, -60, GETDATE())

That is, the date of birth is before today minus 60 years.

Why doesn't DATEDIFF() work? It counts the number of year boundaries between two dates. This year is 2021. So anyone born in 1961 would evaluate to 60. However, today is May 23rd, so only people born on or before May 23rd, 1961 are really 60 years old.

As a note: TIMESTAMPDIFF() works a bit differently in MySQL so it is better for calculating ages correctly. However, I still recommend the simple date comparison approach, even in that database.

Kazi Mohammad Ali Nur Romel · Answer 3 · 2021-05-23T12:49:50.020

In sql server you need to use getdate() or CURRENT_TIMESTAMP instead of curdate() and datediff instead of timestampdiff

SELECT t.TeacherID, t.TeacherFName, t.TeacherLName,DOB
FROM TEACHER t INNER JOIN CLASS c
ON t.TeacherID = c.TeacherID 
WHERE datediff(YEAR, DOB, getdate())>=60;

datediff(YEAR, DOB, getdate()) counts a year if year is changed. That means datediff(YEAR, '31 dec 2020', '1 jan 2021') will return 1. So above query will consider any teacher born in 1961 as 60 years old. If you want to calculate exact 60 years, that means if you are executing the query today and only teachers born in 23-may-1961 at exactly same time or earlier should be considered as 60 and above then as Gordon suggested please use below query.

SELECT t.TeacherID, t.TeacherFName, t.TeacherLName,DOB
FROM TEACHER t INNER JOIN CLASS c
ON t.TeacherID = c.TeacherID 
WHERE dob<=DATEADD(YEAR, -60, GETDATE())

Above query will subtract exactly 60 years from current date with exact time to calculate 60 years. If you want to compare date only regardless of born time then you can consider below query.

SELECT t.TeacherID, t.TeacherFName, t.TeacherLName,DOB
FROM TEACHER t INNER JOIN CLASS c
ON t.TeacherID = c.TeacherID 
WHERE dob<=dateadd(YEAR, -60, cast(getdate()as date))

@user15899456 . . . See my answer. This answer is not correct. — Gordon Linoff, May 23 '21 at 12:08

How to calculate age in SQL

3 Answers3