Sometimes we need to calculate the age of a person from his/her date of birth. In the following example we will calculate the Age from DOB (date of birth) and Current Date.
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'
