YEAR(GETDATE()) - YEAR([dobcolumn]) -
(SIGN
(DATEPART ("dy", [dobcolumn]) -
DATEPART ("dy", GETDATE()))
== 1 ? 1 : 0)
The magic is all in the last part there. Translated into english, it says "subtract the day of the year of the dobcolum and the day of the year of today's date. If that result is positive (i.e. the dob is later in the year than today), then subtract 1 from the total". The ternary operator was the magic bit.
2 comments:
Great, thank you.
This doesn't quite work where leap years are involved and the two month/day parts are within a day of each other - my birthday is the same day each year, it doesn't get moved earlier in a leap year, so it doesn't fall on the same absolute 'day of year', and as such counting the days elapsed in the year doesn't quite work. Eg:
2012-06-07 -> 2015-06-07
Are Day 159 and Day 158 respectively, so the expression incorrectly calculates the diff as 3-1, and returns 2.
I use the approach of formatting both dates as YYYYMMDD strings, subtracting one from the other as ints, divide by 10,000 and return the floor/int of the result. This neatly does day and month compensation without requiring the language to be aware of any particular date concepts. In SSIS it looks something like:
FLOOR(
((DT_I4)((DT_WSTR,4)YEAR(@[User::DATE2]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DATE2]), 2) + RIGHT("0" + (DT_WSTR,2)DAY( @[User::DATE2]), 2))
-
(DT_I4)((DT_WSTR,4)YEAR(@[User::DATE1]) + RIGHT("0" + (DT_WSTR,2)MONTH(@[User::DATE1]), 2) + RIGHT("0" + (DT_WSTR,2)DAY( @[User::DATE1]), 2)))
/10000)
and in sql something like
floor(
convert(int,(CONVERT(VARCHAR(10), @endDate, 112))
-
convert(int,CONVERT(VARCHAR(10),@startDate,112)))
/10000)
Post a Comment