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.

StringFormats in Binding Expressions

Do I have a bee in my bonnet about Culture settings? Maybe I do, but in this case it's a little annoying and it's to do with String.Format (quite possibly my favourite method in the whole of the .NET framework). So in WPF, if I do something like:

MessageBox.Show(string.Format("Price: {0:C}", 123.45));

I get a little message box showing me "Price: £123.45" (because my regional settings are en-GB, you see).

If, however, I use a binding expression like this one:

Binding="{Binding Path=Price, StringFormat={}{0:C}}"

It doesn't matter what my regional settings are, it'll always show up as USD. Now I can kind of see why this might be - the binding expression is stored in an xml file and of course xml files can have language settings, but it does smack to me of a bit of a miss, a dare-I-say-it, "design feature". After a bit of digging around, notably this forum discussion, I came up with the following, less coding solution. Place the following code in your App.xaml code-behind:

protected override void OnStartup(StartupEventArgs e)
{
  FrameworkElement.LanguageProperty.OverrideMetadata(
    typeof(FrameworkElement),
    new FrameworkPropertyMetadata(
      XmlLanguage.GetLanguage(
        CultureInfo.CurrentCulture.IetfLanguageTag)));

  base.OnStartup(e);
}

Clearly, this will only work if you want EVERY element to use your local settings.