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
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/