Occasionally, I like updating old articles that I’ve written on Simple-Talk. Since I used to help Robyn Page with hers, I now and then do the same for her articles as well, with the kind permission of Simple-Talk’s editor. I was rather amused by this DateTime function, that was just derived from one of her examples in her classic ‘Robyn Page’s SQL Server DATE/TIME Workbench’. I extended it to calculate the date, for any year of the first, second, third or fourth weekday (Sunday, Monday, Tuesday etc) in any month. Actually, it will calculate the date of the ‘n’th occurrence of any particular day of the week after the first of the month, even if it occurs in subsequent months. This calculation can cause difficulties because the day of the week is numbered 1-7, and can start at any day, depending on the language setting. To make things more complicated, this setting (@@Datefirst) can be changed independently. No calculations on the day of the week can be dependent on this alarmingly mutable setting. Robyn was struggling with this and asked for my help. I had drunk a rather potent cup of coffee and so, with my temples thumping, I keyed out what seemed a simple expression to get around the problem. I’m not sure that I can explain it now but, mercifully, it seems to work, and works independently of the @@Datefirst setting. I decided to do it as a single query so that the basic expression could also be used ‘in line’ to avoid using a scalar function, but I’ve done it as a scalar function here just to keep things simple. Speed-freaks may prefer to redo it as an inline table-valued function to avoid the performance problems of scalar functions.

I expect that you will find this function rather simple. If so, would you please do a similar function, independent of your @@DateFirst setting, to calculate such holidays as Arbor Day (Last Friday in April) or Memorial Day (Last Monday in May)