Does this query successfully find the first Monday of any given date's month?

Question

My server is configured such that Sunday is day 0 and Monday is day 1. In order to find the first Monday of any given date's month, I have written this:

SELECT DATEADD(DAY,
                   - ((DATEPART(WEEKDAY, DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -1, [DATE])))) %7) - 1),
                  DATEADD(DAY, 1, EOMONTH(DATEADD(MONTH, -1, [DATE]))))

Does this work as intended? Moreover, is there a better or more standard way? I find it very surprising that there's an EOMONTH function but nothing for the start of a month.

You may want to invest in a Calendar table. This and so much more become easy — John Cappelletti, Sep 16 '22 at 20:42
@JohnCappelletti Would if I could. Mind, using the pathetic calendar table that we have to test the code in this question is an idea that I was very stupid not to have. — J. Mini, Sep 16 '22 at 21:40
"Does this work as intended?" - you can verify this faster than we can. — Dale K, Sep 16 '22 at 21:56

score 0 · Answer 1 · answered Sep 17 '22 at 16:10

0

 Select dateadd(day, (7 - datediff(day, -53690, mm.FirstOfMonth) % 7) % 7, mm.FirstOfMonth)
   From (Values (dateadd(month, datediff(month, 1, getdate()), 0))) As mm(FirstOfMonth)

answered Sep 17 '22 at 16:10

Jeff

512
2
8

Your answer could be improved by adding more information on what the code does and how it helps the OP. – Tyler2P Sep 19 '22 at 20:57

Does this query successfully find the first Monday of any given date's month?

1 Answers1