RE: In SQL how to calculate how many days ago last Monday

yes Alan, my bad, this should work.
SELECT
DAYOFWEEK(CURRENT TIMESTAMP) as curday#,
case
when DAYOFWEEK(cadate) <=2 then 5+ DAYOFWEEK(cadate)
else DAYOFWEEK(CURRENT TIMESTAMP)-2
end as Last_monday_days_ago,
CASE DAYOFWEEK(CURRENT TIMESTAMP)
WHEN 1
THEN 'SUNDAY'
WHEN 2
THEN 'MONDAY'
WHEN 3
THEN 'TUESDAY'
WHEN 4
THEN 'WEDNESDAY'
WHEN 5
THEN 'THURSDAY'
WHEN 6
THEN 'FRIDAY'
ELSE
'SATURDAY'
END AS DAYTEXT

FROM SYSIBM.SYSDUMMY1;

By the way, decades ago I had many date issues because the vendor that wrote our initial system used decimal dates.
So I created a calendar table that holds every possible combination of a date, including a date data type, DOW, etc. We even integrated work days, holidays, etc.
that is very useful for things like this, and things like computing aging in work days, etc.
Here is what the sql would be using that concept. I can give you the file layout if your interested.

select cadate,days(current date) - days(cadate) as Last_monday_days_ago
from calndr
where cadow='Monday' and cadate<=current date and cadate >=current date - 7 days

-----Original Message-----
From: Tom Hambel
Sent: Wednesday, September 1, 2021 9:57 AM
To: midrange-l@xxxxxxxxxxxxxxxxxx
Subject: RE: In SQL how to calculate how many days ago last Monday

SELECT
DAYOFWEEK(CURRENT TIMESTAMP) as curday#,
case
when DAYOFWEEK(CURRENT TIMESTAMP) =2 then 7
else DAYOFWEEK(CURRENT TIMESTAMP)-2
end as Last_monday_days_ago,
CASE DAYOFWEEK(CURRENT TIMESTAMP)
WHEN 1
THEN 'SUNDAY'
WHEN 2
THEN 'MONDAY'
WHEN 3
THEN 'TUESDAY'
WHEN 4
THEN 'WEDNESDAY'
WHEN 5
THEN 'THURSDAY'
WHEN 6
THEN 'FRIDAY'
ELSE
'SATURDAY'
END AS DAYTEXT


FROM SYSIBM.SYSDUMMY1;

CENTRAL SEMICONDUCTOR CORP. CONFIDENTIALITY NOTICE: This electronic mail transmission may be privileged, contain trade secrets, or otherwise confidential information and should be read or retained only by the intended recipient. If you have received this transmission in error, you are hereby notified that any review, copying or distribution of it is strictly prohibited. Please inform us immediately and destroy the original transmittal. Thank you for your cooperation.