How to calculate age from Date of Birth in SQL?

To convert the date of birth to age in SQL, we can use the DATEDIFF function. The DATEDIFF function calculates the difference between two dates, expressed in a specified date part, such as years, months, or days.

To convert date of birth to age, you would subtract the date of birth from the current date or a specified date, and express the difference in years.

Here is an example of how to convert date of birth to age in SQL using the DATEDIFF function:

SELECT name, DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age
FROM employees;

In this example, name and date_of_birth are columns in the employees table, and GETDATE() returns the current date.

The DATEDIFF function calculates the difference in years between the date_of_birth and the current date, and the result of the calculation is stored in a new column age. This query returns the name and age of each employee in the employees table.

Let’s take an example to understand it in detail:

Example 1:

Here is an example of how you could calculate age from date of birth in SQL using a sample table:

CREATE TABLE people (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE
);
INSERT INTO people (id, name, date_of_birth)
VALUES (1, 'John Doe', '1980-01-01'),
(2, 'Jane Doe', '1985-05-01'),
    (3, 'Jim Smith', '1990-08-01');

This creates a table named people with three rows of sample data. To calculate the age of each person, you can use the following query:

SELECT name, DATEDIFF(YEAR, date_of_birth, GETDATE()) AS age
FROM people;

This query returns the name and age of each person in the people table. The DATEDIFF function calculates the difference in years between the date_of_birth and the current date, returned by GETDATE(). The result of the calculation is stored in a new column age.

Output:

Here is what the result of the above query would look like:

Example 2:

Here is another example of how you could calculate age from date of birth in SQL using a sample table:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE,
hire_date DATE
);
INSERT INTO employees (id, name, date_of_birth, hire_date)
VALUES (1, 'Alice', '1980-01-01', '2010-01-01'),
       (2, 'Bob', '1985-05-01', '2015-01-01'),
       (3, 'Charlie', '1990-08-01', '2020-01-01');

This creates a table named employees with three rows of sample data. To calculate the age of each employee as of their hire date, you can use the following query:

SELECT name, DATEDIFF(YEAR, date_of_birth, hire_date) AS age_at_hire
FROM employees;

This query returns the name and age of each employee in the employees table as of their hire date. The DATEDIFF function calculates the difference in years between the date_of_birth and the hire_date. The result of the calculation is stored in a new column age_at_hire.

Here is what the result of the above query would look like:

Different methods to calculate age from the date of birth

Here is another method to calculate age from date of birth in SQL:

SELECT name, 
FLOOR(DATEDIFF(day, date_of_birth, GETDATE()) / 365.25) AS age
FROM employees;

In this method, FLOOR rounds down the result of the calculation to the nearest whole number. The calculation itself uses the DATEDIFF function to find the difference in days between the date_of_birth and the current date, returned by GETDATE(). The result is divided by 365.25, which is the average number of days in a year including leap years.

This method provides a more accurate representation of age, accounting for leap years, and gives the same result as the previous method. The result of this query would be the same as the previous example, with the names and ages of each employee in the employees table.

Example 3:

CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
date_of_birth DATE,
hire_date DATE
);
INSERT INTO employees (id, name, date_of_birth, hire_date)
VALUES (1, 'Akash', '1980-01-01', '2010-01-01'),
       (2, 'Bunty', '1985-05-01', '2015-01-01'),
       (3, 'Chaplin', '1990-08-01', '2020-01-01');

As we all know that this table is created with three sample row of data. If we want to calculate the age of each employee as of the current date, then we can use the following queries:

SELECT name, 
FLOOR(DATEDIFF(day, date_of_birth, GETDATE()) / 365.25) AS age
FROM employees;

The FLOOR function rounds down the result of the calculation to the nearest whole number. The calculation itself uses the DATEDIFF function to find the difference in days between the date_of_birth and the current date, returned by GETDATE().

The result is divided by 365.25, which is the average number of days in a year including leap years.

Here's what the result of the above query would look like, assuming the current date is 2023-02-01: