forum

Home / DeveloperSection / Forums / SQL Query to Calculate Age from Date of Birth in SQL Server

Ask Question

SQL Query to Calculate Age from Date of Birth in SQL Server

Anubhav Kumar14715-Jul-2024

Hello,

I need a query to calculate the age of employees from their DateOfBirth column in the Employee table. How can I do this in SQL Server?

SQL Serverdatabase sql server sql server 2008 
Updated on 16-Jul-2024
Anubhav Kumar

Anubhav Kumar

Follow
The Anubhav portal was launched in March 2015 at the behest of the Hon'ble Prime Minister for retiring government officials to leave a record of their experiences while in Govt service .

Can you answer this question?

Answer

1 Answers

Ravi Vishwakarma

Ravi Vishwakarma

16-Jul-2024

To calculate age from date of birth in SQL Server, you can use the DATEDIFF function along with GETDATE() to get the current date. 

Here's an example query:

Example 1

SELECT 
    UserID,
    DateOfBirth,
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) AS Age
FROM Users;

DATEDIFF(YEAR, DateOfBirth, GETDATE()): Calculates the difference in years between the 

current date (GETDATE()) and the DateOfBirth.

Example 2

-- Assuming you have a table named 'Users' with columns 'UserID' and 'DateOfBirth'
SELECT 
    UserID,
    DateOfBirth,
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) - 
        CASE
            WHEN DATEADD(YEAR, DATEDIFF(YEAR, DateOfBirth, GETDATE()), DateOfBirth) > GETDATE() 
                THEN 1 
            ELSE 0 
        END AS Age
FROM Users;

CASE ... END: Adjusts the age if the birth date for this year hasn't occurred yet. It subtracts 1 from the age if the birthday hasn't occurred in the current year.

This query assumes you have a table named Users with columns UserID and DateOfBirth. Adjust the table and column names as per your database schema.

 

How do I use CTE to simplify complex queries in SQL Server?

Explain the Aggregate functions in the SQL server.

Explain the transaction in SQL Server.

MERGE statement in SQL Server to perform upserts

MindStick Training

Liked By