Recently, I was working on a project in which I had to write a SQL query for getting age in years and then filter the records based on the age. In the underlying database, I had a table that stored the date of birth of people along with other details. In this example, I have explained how to calculate age from date of birth in SQL.

Database Structure

For demonstration purposes, I have created a sample database that consists of a table with some dummy records. The queries for creating the sample database are given below:

CREATE DATABASE SchoolDB;

USE SchoolDB;

CREATE TABLE Students
(
	Id				INT			IDENTITY	PRIMARY KEY,
	Name			VARCHAR(30)		NOT NULL,
	DateOfBirth		DATETIME		NOT NULL
);

INSERT INTO Students VALUES ('Anil Sood', '15-NOV-1955');
INSERT INTO Students VALUES ('Neena Sood', '19-NOV-1959');
INSERT INTO Students VALUES ('Varun Sood', '28-MAY-1987');
INSERT INTO Students VALUES ('Priyanka Sood', '02-MAR-1992');
INSERT INTO Students VALUES ('Avikshit Sood', '22-MAY-2008');

SELECT * FROM Students;

Once the database structure has been created, we can write a SQL query for getting age in years as follows:

WITH AgeCTE AS
(
	SELECT Name, DateOfBirth, CASE WHEN DATEADD(yy, DATEDIFF(yy, DateOfBirth, GETDATE()), DateOfBirth) < GETDATE() THEN DATEDIFF(yy, DateOfBirth, GETDATE()) ELSE DATEDIFF(yy, DateOfBirth, GETDATE()) - 1 END AS Age FROM Students
)
SELECT * FROM AgeCTE

This query yields a result set as shown below:

Name			DateOfBirth			Age
Anil Sood		1955-11-15 00:00:00.000		62
Neena Sood		1959-11-19 00:00:00.000		57
Varun Sood		1987-05-28 00:00:00.000		30
Priyanka Sood		1992-03-02 00:00:00.000		25
Avikshit Sood		2008-05-22 00:00:00.000		9

You can also extend the query using aggregate functions and clauses like:

WITH AgeCTE AS
(
	SELECT Name, DateOfBirth, CASE WHEN DATEADD(yy, DATEDIFF(yy, DateOfBirth, GETDATE()), DateOfBirth) < GETDATE() THEN DATEDIFF(yy, DateOfBirth, GETDATE()) ELSE DATEDIFF(yy, DateOfBirth, GETDATE()) - 1 END AS Age FROM Students
)
SELECT COUNT(*) AS [Number of People] FROM AgeCTE WHERE (Age < 30)

The output of the above query would be:

Number of People
2

I hope you have understood how to calculate age from date of birth in SQL. In case you have any doubts, please ask me questions in the comments section below. If you want to learn more, have a look at the post Display a Confirmation Dialog from Server Side in ASP.NET which explains a very useful trick in ASP.NET.