This seems to be a common issue, which I cannot seem to find the answer for, so hoping for some new insight please.

I need to find the previous Monday's date, unless the date passed to the statement is itself a Monday, then I need to use that date.

I have tried the following

DATEADD(wk, DATEDIFF(wk,0,StartDate), 0)

This returns the correct date, unless it is a Sunday, then it returns the next Monday instead of the last Monday.

DATEADD(wk, DATEDIFF(wk,0,StartDate), -1)

This returns Sunday instead of Monday.

DATEADD(DAY, 1 - DATEPART(WEEKDAY, StartDate), StartDate)

This also returns Sunday.

Any thoughts on how to fix this?

CodePudding user response：

Using Greg Hewgill's answer as inspiration, one method would be to use their expression to get an unambiguous day number, with Monday as 0 and Sunday as 6, then then subtract that day number from the current date:

SELECT DATEADD(DAY,-(DATEPART(WEEKDAY,GETDATE())    @@DATEFIRST   5) % 7,CONVERT(date,GETDATE()))

Alternatively, on the latest version of the data engine, you can use DATE_BUCKET. Unlike DATEPART, DATE_BUCKET isn't affected by the language settings for things like when a week starts. When its 4th parameter is omitted, it always uses 1900-01-01 as it's start date, which conveniently (here) is a Monday. This means that with that parameter omitted, asking for the date the "week bucket" started will return a Monday as well.

SELECT DATE_BUCKET(WEEK,1,GETDATE());

Also inspired by El.MENSI's now deleted answer, who attempted to use DATE_TRUNC (possibly MySQL syntax), though you can't use WEEK for the truncation, you could use ISO_WEEK, which always starts on a Monday. This too, however, is only supported on the latest version of the data engine:

SELECT DATE_TRUNC(ISO_WEEK, GETDATE());