Calculate Age in SQL Server


So you have a birthdate and want to calculate how old someone is, but you might not understand the math. Siddhartha Narayan Pal has a post on the subject where he gives some Transact-SQL script to calculate an age based on the date that person was born. You can read his entire post for more information.

       ,@CurrentDate DATE
       ,@YearsAge INT
       ,@MonthsAge INT
       ,@DaysAge INT
       ,@LastBirthDay DATE
      ,@LastMonthDate DATE
SET @DOB   = '2002-03-31'
SET @CurrentDate = GETDATE()
@YearsAge = YEAR(@CurrentDate)-YEAR(@DOB)
    - CASE  WHEN MONTH(@CurrentDate) < MONTH(@DOB)
              OR (MONTH(@CurrentDate) = MONTH(@DOB)
                                  and DAY(@CurrentDate) < DAY(@DOB))
            THEN 1
            ELSE 0
,@LastBirthDay = DATEADD(year,@YearsAge,@DOB)
,@MonthsAge = DATEDIFF(month, @LastBirthDay, @CurrentDate)
    - CASE  WHEN MONTH(@CurrentDate) <= MONTH(@LastBirthDay)
                                  and DAY(@CurrentDate) < DAY(@LastBirthDay)
            THEN 1
            ELSE 0
    - CASE  WHEN MONTH(@DOB) = 2 and DAY(@DOB) = 29
            THEN 1
            ELSE 0
,@LastMonthDate = DATEADD(month,@MonthsAge,@LastBirthDay)
,@DaysAge = CASE  WHEN DAY(@CurrentDate) >= DAY(@LastMonthDate)
                           THEN DATEDIFF(day, @LastMonthDate, @CurrentDate)
                           ELSE DATEPART(day,@LastMonthDate) - DATEDIFF(day, @CurrentDate, @LastMonthDate)
SELECT 'Your Age is: ' + CONVERT(VARCHAR,@YearsAge) + ' Years ' +
CONVERT(VARCHAR,@MonthsAge) + ' Months and ' +
CONVERT(VARCHAR,@DaysAge) + ' Days'

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s