To get age in Year, month and date, we need to manipulate the given date with
DATEDIFF and other
DECLARE @givenDate datetime DECLARE @tempDate datetime DECLARE @years int, @months int, @days int SELECT @givenDate = '01/24/1977' SELECT @tempDate = @givenDate -- get year SELECT @years = DATEDIFF(yy, @tempDate, GETDATE()) - CASE WHEN (MONTH(@givenDate) > MONTH(GETDATE())) OR (MONTH(@givenDate) = MONTH(GETDATE()) AND DAY(@givenDate) > DAY(GETDATE())) THEN 1 ELSE 0 END SELECT @tempDate = DATEADD(yy, @years, @tempDate) -- get months SELECT @months = DATEDIFF(m, @tempDate, GETDATE()) - CASE WHEN DAY(@givenDate) > DAY(GETDATE()) THEN 1 ELSE 0 END SELECT @tempDate = DATEADD(m, @months, @tempDate) -- get days SELECT @days = DATEDIFF(d, @tempDate, GETDATE()) -- output the result SELECT CAST(@years as varchar(5)) + ' years ' + CAST(@months as varchar(3)) + ' months ' + CAST(@days as varchar(3)) + ' days'
In the above code snippet, we are declaring few variables to hold given date, temporary date to calculate on and calculated date, months and year.
First, we are calculating the year portion of the age by subtracting the year from 1 or 0 based on whether the current month is greater than given month or not.
Next, we are calculating months and the days and at last we are casting years, months and days values into varchar and giving as output.
Views: 4132 | Post Order: 77