A very common requirement is to calculate aging. With the help of below SQL script, we will calculate the age very easily.
|
DECLARE @MTH INT,
@Years INT,
@Months INT,
@Dates INT,
@GETDays INT
DECLARE @STARTDATE AS DATETIME,
@ENDDATE AS DATETIME
SET @STARTDATE = format(cast('03/01/1988' as date),'yyyyMMdd')
SET @ENDDATE = GETDATE()
SET @Years = DATEDIFF(YY, @STARTDATE, @ENDDATE)
SET @Months = DATEDIFF(MM, @STARTDATE, @ENDDATE)
SET @MTH = CASE
WHEN DATEPART(DD, @ENDDATE) <= DATEPART(DD, @STARTDATE)
THEN ABS(DATEDIFF(MM, DATEADD(YY, @Years, @STARTDATE), @ENDDATE)) - 1
ELSE ABS(DATEDIFF(MM, DATEADD(YY, @Years, @STARTDATE), @ENDDATE))
END
SET @GETDays = CASE
WHEN DATEPART(DD, @ENDDATE) <= DATEPART(DD, @STARTDATE)
THEN (DATEPART(DD, @ENDDATE) + DAY(EOMONTH(DATEADD(MM, - 1, GETDATE())))) - DATEPART(DD, @STARTDATE)
ELSE DATEPART(DD, @ENDDATE) - DATEPART(DD, @STARTDATE)
END
SELECT CONVERT(VARCHAR(10), @Years) + ' YEAR(s) ' + CONVERT(VARCHAR(10), @MTH) + ' MONTH(s) ' + CONVERT(VARCHAR(10), @GETDays) + ' DAY(s)' Age
|
The input we need to give as MM/DD/YYYY format.
Here I am calculating my age. My Birth date is 03/01/1988. See the output.
