Calculate Age from Date Of Birth in SSIS

Just responded to an interesting question about working out someone's age from their date of birth in an SSIS derived column transform. My first reaction was to use a script task (I've done it so often in C# that I can do it without thinking). But I thought it'd be nice to work out how to do it in a devired column. So here it is:


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.