Do you want to know your EXACT AGE? Here Is the Code.

kgovindarao523-21772
Posted by kgovindarao523-21772 under Sql Server category on | Points: 40 | Views : 472
Hi,
You can check your exact age including years, months, days by giving your date of birth in year(2000)-month(01)-day(01) format, using below code.
DECLARE @from DATETIME, 
@to DATETIME,
@init INT,
@carry INT,
@const INT,
@result INT

--Please enter Your date Of birth here- it must be in 'yyyy-MM-dd' format
SET @from ='1990-01-01'

SET @to = GetDate()

SET @carry = 0
DECLARE @YEARS SMALLINT, @MONTHS SMALLINT, @DAYS SMALLINT


IF(@from > @to)
BEGIN
PRINT 'ERROR : Fromdate must be less than or equal to ToDate'
RETURN
END

--calculate days

SET @init = DATEPART (dd, @to) - DATEPART (dd, @from)
DECLARE @LastdayOfMonth INT = CASE WHEN @init<0 THEN DATEPART(dd,DATEADD(dd,-(DAY(@to)),@to)) ELSE DATEPART(dd,DATEADD(dd,-(DAY(DATEADD(mm,1,@to))),DATEADD(mm,1,@to))) END
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 ELSE @LastdayOfMonth END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN SIGN(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @DAYS = REPLICATE('0',2-len(@result)) + CONVERT(VARCHAR, @result)

--calculate Months
SET @init = DATEPART (MM, @to) - DATEPART (MM, @from)
SET @const = 12
SELECT @const = CASE WHEN @init = 0 AND @carry = 0 THEN 0 else @const END
SELECT @result = CASE WHEN @init <= 0 THEN @const + @init + @carry ELSE @init + @carry END
SELECT @carry = CASE WHEN SIGN(@init) = -1 OR (@init = 0 AND @carry = -1) THEN -1 ELSE 0 END
SELECT @MONTHS = REPLICATE('0',2-len(@result)) + CONVERT(VARCHAR, @result)

--calculate years
SET @init = DATEPART (yy, @to) - DATEPART (yy, @from)
SET @const = 0
SELECT @result = CASE WHEN @carry = -1 then @const + @init + @carry ELSE @init + @carry END
SELECT @YEARS = REPLICATE('0',2-LEN(@result)) + CONVERT(VARCHAR, @result)

SELECT 'Your Age is:' AS MESSAGE, @YEARS AS YEARS, @MONTHS AS MONTHS, @DAYS AS DAYS



Output
MESSAGE	      YEARS   MONTHS	 DAYS
--------------------------------------
Your Age is: 24 10 16


Thank You,
Govind.

Comments or Responses

Login to post response