How to Calculate Age from Date of Birth in SQL Server

As a database developer, I’ve often been asked to retrieve age information from birth dates stored in SQL Server. In this comprehensive article, I’ll walk you through multiple approaches to calculate age from date of birth in SQL Server.

How to Calculate Age from Date of Birth in SQL Server

Before diving into the code, let’s understand why calculating age directly in SQL Server is beneficial:

• Performance: Processing age calculations at the database level reduces data transfer and application processing overhead
• Consistency: Ensures uniform age calculation across all application layers
• Efficiency: Enables filtering, sorting, and reporting on age without retrieving entire datasets

Approach 1: Using DATEDIFF for Basic Age Calculation

The most straightforward approach to calculating age in SQL Server is using the DATEDIFF function. This function calculates the difference between two dates in a specified interval.

SELECT DATEDIFF(YEAR, DOB, GETDATE()) AS Age
FROM StudentDetail

After executing the above query, I got the expected output as shown in the screenshot below

Approach 2: The Accurate Year Calculation

We can implement a more precise calculation that checks if the birthday has occurred this year:

SELECT 
    DATEDIFF(YEAR, DOB, GETDATE()) - 
    CASE 
        WHEN (MONTH(DOB) > MONTH(GETDATE())) OR 
             (MONTH(DOB) = MONTH(GETDATE()) AND DAY(DOB) > DAY(GETDATE()))
        THEN 1 
        ELSE 0 
    END AS Age
FROM StudentDetail

This CASE statement approach checks if the birth month and day have occurred in the current year. If not, we subtract 1 from the DATEDIFF result. After executing the above query, I got the expected output as shown below.

Example Application

Consider our studentdetail table with the following data:

Running our query on July 15, 2025 would give us:

Approach 3: Creating a Reusable Age Function

For better code organization and reusability, I recommend creating a dedicated function to calculate age. This approach allows you to call the age calculation consistently across your database:

CREATE FUNCTION dbo.CalculateAge
(
    @DOB DATE,
    @CurrentDate DATE = NULL
)
RETURNS INT
AS
BEGIN
    -- If no current date provided, use today's date
    IF @CurrentDate IS NULL
        SET @CurrentDate = GETDATE()
        
    RETURN DATEDIFF(YEAR, @DOB, @CurrentDate) - 
        CASE 
            WHEN (MONTH(@DOB) > MONTH(@CurrentDate)) OR 
                 (MONTH(@DOB) = MONTH(@CurrentDate) AND DAY(@DOB) > DAY(@CurrentDate))
            THEN 1 
            ELSE 0 
        END
END

The function was created successfully after executing the above query, as shown below.

You can then use this function in any query:

SELECT 
    StudentName, 
    country, 
    DOB, 
    dbo.CalculateAge(DOB, GETDATE()) AS Age
FROM StudentDetail

I got the expected output after executing the above query as shown in the screenshot below.

This approach offers flexibility and consistency across your application.

Approach 4: Getting Detailed Age in Years, Months, and Days

Sometimes you need more precision than just years. For these cases, we can calculate age in years, months, and days using a more comprehensive approach:

CREATE PROCEDURE dbo.GetDetailedAge
    @DateOfBirth DATE
AS
BEGIN
    DECLARE @CurrentDate DATE = GETDATE()
    DECLARE @Years INT, @Months INT, @Days INT
    
    -- Calculate years
    SET @Years = DATEDIFF(YEAR, @DateOfBirth, @CurrentDate) - 
                CASE 
                    WHEN (MONTH(@DateOfBirth) > MONTH(@CurrentDate)) OR 
                         (MONTH(@DateOfBirth) = MONTH(@CurrentDate) AND DAY(@DateOfBirth) > DAY(@CurrentDate))
                    THEN 1 
                    ELSE 0 
                END
    
    -- Calculate the date at exactly @Years years after birth
    DECLARE @YearsAfterBirth DATE = DATEADD(YEAR, @Years, @DateOfBirth)
    
    -- Calculate months
    SET @Months = DATEDIFF(MONTH, @YearsAfterBirth, @CurrentDate) - 
                 CASE 
                     WHEN DAY(@DateOfBirth) > DAY(@CurrentDate)
                     THEN 1 
                     ELSE 0 
                 END
    
    -- Calculate the date at exactly @Months months after @YearsAfterBirth
    DECLARE @MonthsAfterYears DATE = DATEADD(MONTH, @Months, @YearsAfterBirth)
    
    -- Calculate days
    SET @Days = DATEDIFF(DAY, @MonthsAfterYears, @CurrentDate)
    
    -- Return the results
    SELECT @Years AS Years, @Months AS Months, @Days AS Days
END

This stored procedure provides a detailed breakdown of someone’s age, which is particularly useful for applications that need precise age calculations.

Approach 5: Using Computed Columns for Age

If you frequently need to query based on age, consider adding a computed column to your table:

ALTER TABLE Employees
ADD Age AS (
    DATEDIFF(YEAR, DateOfBirth, GETDATE()) - 
    CASE 
        WHEN (MONTH(DateOfBirth) > MONTH(GETDATE())) OR 
             (MONTH(DateOfBirth) = MONTH(GETDATE()) AND DAY(DateOfBirth) > DAY(GETDATE()))
        THEN 1 
        ELSE 0 
    END
)

The advantage of this approach is that you can directly query, filter, or sort by age without recalculating it each time:

-- Find all students who are 40 or older
SELECT StudentName, country, AgeN
FROM StudentDetail
WHERE AgeN >= 30
ORDER BY AgeN DESC

Remember that this computed column will be recalculated whenever it’s accessed, ensuring the age is always up-to-date.

Performance Considerations

When dealing with large datasets, consider these performance tips:

• Indexing: If you frequently filter by age, consider indexing your DateOfBirth column
• Computed Columns: For tables with frequent age-based queries, use persisted computed columns
• Batch Processing: For large reports, process age calculations in batches
• Caching: Consider caching age results for reports that don’t need real-time accuracy

Video Tutorial

Conclusion

Calculating age from date of birth in SQL Server is a fundamental skill for database developers and data analysts.

It is recomanded using the accurate year calculation method (Approach 2) or creating a dedicated age calculation function (Approach 3) for better code organization and reusability.

Remember these key takeaways:

• Simple DATEDIFF calculations aren’t sufficient for accurate age calculations
• Always check if the birth date has occurred in the current year
• Consider creating a dedicated function for consistent age calculations
• For precision-critical applications, account for leap years and time zones
• Validate date inputs to prevent calculation errors
• Optimize for performance when dealing with large datasets

By implementing these approaches, you’ll ensure accurate, efficient age calculations in your SQL Server database.

You may also like the following articles.

• SQL Server First Date Of Month
• SQL Server Short Date
• SQL Server Weekday From Date
• Add Month To Date SQL Server

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.