Jeff Moden (11/3/2012)

---

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