Monday, March 4, 2013

calculate age or get the duration between two dates in years, months and days


ALTER FUNCTION [dbo].[calc_age]
(
-- Add the parameters for the function here
@date smalldatetime
)
RETURNS nvarchar(4000)
AS
BEGIN
-- Declare the return variable here
DECLARE  @tmpdate datetime, @years int, @months int, @days int

SELECT @tmpdate = @date
SELECT @years = DATEDIFF(yy, @tmpdate, GETDATE()) - CASE WHEN (MONTH(@date) > MONTH(GETDATE())) OR (MONTH(@date) = MONTH(GETDATE()) AND DAY(@date) > DAY(GETDATE())) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)
SELECT @months = DATEDIFF(m, @tmpdate, GETDATE()) - CASE WHEN DAY(@date) > DAY(GETDATE()) THEN 1 ELSE 0 END
SELECT @tmpdate = DATEADD(m, @months, @tmpdate)
SELECT @days = DATEDIFF(d, @tmpdate, GETDATE())
return  CAST(@years as nvarchar(50)) + ' Years '  + CAST(@months as nvarchar(50)) + ' Months ' + CAST(@days as nvarchar(50)) +' Days '
END

No comments:

Post a Comment