Calculate Age in SQL Server

programming-transactsql

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.

DECLARE @DOB DATE
       ,@CurrentDate DATE
       ,@YearsAge INT
       ,@MonthsAge INT
       ,@DaysAge INT
       ,@LastBirthDay DATE
      ,@LastMonthDate DATE
SET @DOB   = '2002-03-31'
SET @CurrentDate = GETDATE()
SELECT
@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
      END
,@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
      END
    - CASE  WHEN MONTH(@DOB) = 2 and DAY(@DOB) = 29
            THEN 1
            ELSE 0
      END
,@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)
            END
SELECT 'Your Age is: ' + CONVERT(VARCHAR,@YearsAge) + ' Years ' +
CONVERT(VARCHAR,@MonthsAge) + ' Months and ' +
CONVERT(VARCHAR,@DaysAge) + ' Days'
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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