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

1.7k Views Asked by At

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.

1

There are 1 best solutions below

2
Jeff On
 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)