Calculate the previous sundays date from a Date column?

Question

I have a table that contains a field t.Processed_Date. I need to return the Previous sunday of that date. Everything I'm trying is not working.

DATEADD(day,
    -1 - (DATEPART(weekday, GETDATE()) + CAST(Processed_Date As date) - 2) % 7,
    GETDATE()
) As 'Last Sunday'

But this gives an error

Msg 206, Level 16, State 2, Line 3
Operand type clash: date is incompatible with int

Server is MS SQL Server 2017 standard

Does this answer your question? [Get previous Tuesday (or any given day of week) for specified date](https://stackoverflow.com/questions/33079774/get-previous-tuesday-or-any-given-day-of-week-for-specified-date) Note that the top solution there works irrespective of `DATEFIRST` — Charlieface, Nov 22 '21 at 14:44

score 2 · Accepted Answer · answered Nov 22 '21 at 13:07

2

SELECT DATEADD(wk, DATEDIFF(wk, 6, Processed_Date), 6) as LastSunday

answered Nov 22 '21 at 13:07

huMpty duMpty

Neat solution - I'd be curious to see a short explanation of that works. – Peter Smith Nov 22 '21 at 13:49
1

`SET DATEFIRST` is going to mess this up – Charlieface Nov 22 '21 at 14:40
@PeterSmith See https://stackoverflow.com/a/1177529/14868997, it's basically adding the number of whole weeks since "day 6" (`1900-01-07`) to that day, thereby truncating the difference – Charlieface Nov 22 '21 at 14:41

1 Answers1