SQL Age to the Day

This is an old one but something I still use regularly.
SQL Servers DateDiff function does not work out take into consideration Days of the year when determining the difference between two dates. It only looks at the Year portion of the date.

To get around this you have to write something yourself, the below is what I use. I actually put in a udf and use it that way rather than writing it out each time.


DECLARE @BirthDate as datetime
DECLARE @CurrentDate as datetime
SET @BirthDate = '2000-01-02 00:00:00.000'
SET @CurrentDate = '2005-01-02 00:00:00.000'
Select DATEDIFF(YY, @BirthDate, @CurrentDate) -
CASE WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(@CurrentDate)*100 + DAY(@CurrentDate))
) THEN 1 ELSE 0 END

, DATEDIFF(YY, @BirthDate, getdate()) -
CASE WHEN(
(MONTH(@BirthDate)*100 + DAY(@BirthDate)) >
(MONTH(getdate())*100 + DAY(getdate()))
) THEN 1 ELSE 0 END