Calculate Age from Birth Date in SQL Server

Calculating age from a birth date in SQL Server can be done using the DATEDIFF function. This function calculates the difference between two dates in a specified interval (e.g., year, month, day).

Syntax:

SELECT DATEDIFF(year, {birth_date}, GETDATE()) AS Age;

Example:

Let's say we have a table named Employees with a column named BirthDate and we want to calculate the age of each employee.

Table Structure:

CREATE TABLE Employees ( EmployeeID int, Name varchar(50), BirthDate date );

Sample Data:

INSERT INTO Employees (EmployeeID, Name, BirthDate) VALUES (1, 'John Doe', '1990-02-12'), (2, 'Jane Doe', '1985-06-25'), (3, 'Bob Smith', '1970-01-01');

Calculate Age:

SELECT Name, BirthDate, DATEDIFF(year, BirthDate, GETDATE()) AS Age FROM Employees;

Result:

Note that this calculation assumes the current date is the date when the query is executed. If you want to calculate the age as of a specific date, you can replace GETDATE() with that date.

Alternative Method:

You can also use the following formula to calculate age:

SELECT Name, BirthDate, FLOOR((CAST(GETDATE() AS float) - CAST(BirthDate AS float)) / 365.25) AS Age FROM Employees;

This method uses the difference between the current date and the birth date in days, divided by 365.25 (accounting for leap years), and then rounds down to the nearest whole number using the FLOOR function.