2

Here's an interesting equation of sorts. I'm looking for the cleanest way to retrieve the second full-week Fridays of each month for any given year. Assuming the week begins on Monday.

EXAMPLES

  • November 2017 would be the 17th.
  • December 2017 would be the 15th.
  • January 2018 would be the 12th.

These things are fun usually but I'm not feeling it right now. Any suggestions?

pilcrow
  • 56,591
  • 13
  • 94
  • 135
hack3rfx
  • 573
  • 3
  • 16
  • Just take a look at this [date table](https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/) by Aaron Bertrand. You can query it as you like, but you have a very good starting point. – Rigerta Dec 12 '17 at 15:22
  • Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. – HABO Dec 12 '17 at 16:10

3 Answers3

3

This might be not easy to understand at first, but for this specific case rules are simple:

weekday = 'Friday' (or whichever way to determine if this day is Friday)
and day_of_month between 12 and 18

This will work if week starts on Monday.

Edgars T.
  • 947
  • 8
  • 14
  • It really is that simple, excellent take. I tend to overthink these things. – hack3rfx Dec 12 '17 at 15:38
  • Just to note for my own reference and anyone else's, the reason this works so well is because the 2nd full week Friday of every month will fall between the 12th and 18th. The third full week Friday will never come on or after the 18th, and the first full week Friday will never fall before or on the 12th. The use-case is very specific but the 'keep it simple' concept could be applied globally. – hack3rfx Dec 12 '17 at 15:46
1

If work weeks begin on Mondays, then the Friday of the second full work week of a month is

SELECT DATEADD(day, (9 - DATEPART(weekday, @fdom)) % 7) + 11, @fdom)
       AS SecondWorkweekFriday;

-- 1. start with the first day of the month (@fdom)
-- 2. advance to the nearest Monday with modular arithmetic
--    n.b.: by default DATEPART considers Monday to be ordinal
--          week day #2, thus "7 - DATEPART() + 2" becomes
--          "9 - DATEPART"
-- 3. add eleven days to get to the second Friday thereafter

where @fdom is the first day of the month. There are many ways to find the first day of the month in SQL.

pilcrow
  • 56,591
  • 13
  • 94
  • 135
0

Personally I like a date table for things like this, but you can also math it out:

CASE WHEN datepart(weekday, '2017-12-01')<=5 THEN 7 ELSE 14 END + (7-datepart(weekday, '2017-12-01'))

That 5 may need to be tweaked, but I think it's right since datepart(weekday is Sunday based and you are monday based (may need to be 6 to properly offset).

At any rate, this should get you in the ballpark.

JNevill
  • 46,980
  • 4
  • 38
  • 63