• Thom A - Monday, April 10, 2017 9:49 AM

    Thanks Scott, didn't really consider using Math logic. Although, I'll admit, not as readable, does perform quicker. 🙂

    Thanks.  It's not as readable at first, but once you get used to the technique, you'll find it's actually fairly straightforward.  There are two main elements:

    1) SQL Server uses '19000101' as its base date, day 0, and that date is a Monday.  Therefore, 0=Mon,1=Tue,...,6=Sun.  If you always use these day values, you become familiar with them rather quickly.

    2) The 7th of the month is the last day that a given day of the week can first appear.  So, first compute day 7, then back up to the desired day; if the current day is the desired day, don't adjust the day at all.

    Therefore, this code:
    DATEDIFF(DAY, 5, feb_07) % 7
    figures out how many days past the given day of the week the specified date, in this case Feb 07, is, then subtracts that number of days to take the date back to that day.  Note that this calc works exactly the same regardless of the @@DATEFIRST or language settings (day name is never used, only a generic day number).

    If you prefer, you can add a bit more clarity to the code by defining the day value as "Saturday", viz:
    SELECT DATEADD(DAY, -DATEDIFF(DAY, Saturday, feb_07) % 7, feb_07) AS feb_first_sat
    FROM ( SELECT DATEADD(DAY, 37, DATEADD(YEAR, DATEDIFF(YEAR, 0, @Date), 0)) AS feb_07, 5 AS Saturday ) AS assign_alias_name
    Sometimes I'll do that, but given the function name and the result column name, I didn't feel that was needed here 😉

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!