Sean Lange (11/2/2012)
CELKO (11/2/2012)
If my birthday is in December (1983-12-12) and I just did the datediff function using CURRENT_TIMESTAMP and that birthdate...It would return that my age is 29 when really I would still be 28! That is why I have all those if statements! Because those account for those situations!
We have a DATE data type, the ANSI/ISO Standard CURRENT_TIMESTAMP, and we use the ISO-8601 date formats; I corrected your posting. Now it gets worse; which age system do you use? Asians count the year in which you are living (I am in my 66-th year) and Westerners count the last whole year you passed (I was 65 on 2012-01-24).
DATEDIFF (YEAR, '1983-12-12', CAST(CURRENT_TIMESTAMP AS DATE)) = 29
But:
SELECT DATEDIFF (YEAR, '1947-02-24', CAST(CURRENT_TIMESTAMP AS DATE)) = 65
Instead of all that casting and concatenation, you can use:
DATEDIFF (YEAR, @dd, CAST(CURRENT_TIMESTAMP AS DATE)),
CASE SIGN(MONTH(CURRENT_TIMESTAMP) - MONTH (@dd))
WHEN -1 THEN -1 ELSE 0 END
Little puzzle: replace the CASE expression with calls to SIGN() and ABS().
Of if you prefer the really simple method you can just get the months and do integer division.
declare @dd datetime = '1947-2-24'
select datediff(month, @dd, CURRENT_TIMESTAMP)/12
Nice try Sean, but it doesn't work in all cases. Please see the following.
DECLARE @DOB DATETIME
SET @DOB = '2008-12-31'
DECLARE @Now DATETIME
SET @Now = '2009-12-30'
select datediff(month, @DOB, @Now)/12
--Jeff Moden
Change is inevitable... Change for the better is not.