Last monday's date 1

[PatelRam]

Hi,

How can I write a select statement that will retrieve last monday's date without knowing what today's date is? I know that if today where monday and I wanted to know last monday's date, I could just subtract 7 from today's date. If today were Tuesday, I could subtract 8 and so forth. But is there a way that I can use the same select statement to get last monday's date regardless of what day of the week it is now?
Thanks in advance for your help.

 

[PruSQLer]

Here's one way to do it. There are others I'm sure.


select case when dayname(current timestamp) = 'Sunday' then date(current timestamp) - 6 days
when dayname(current timestamp) = 'Monday' then date(current timestamp) - 7 days
when dayname(current timestamp) = 'Tuesday' then date(current timestamp) - 8 days
when dayname(current timestamp) = 'Wednesday' then date(current timestamp) - 9 days
Etc, Etc
end
from sysibm.dummy

 

[Hattusas]

Suppose we a have table finance.accounts with a date field
named as pdate which has a max value of '10/10/2004' (wednesday)
Step 1
SELECT DAYOFWEEK_ISO(MAX(PDATE)) FROM FINANCE.ACCOUNTS
returns 3.. (Week starts on Monday with ISO function)
Step 2
We need to go back to the previous week so we must subtract 7
Step 3
But since monday has a value of 1, we will have to add one so as a total we will subtract 6 (hope I am clear enough )
Step 4
DAYS function returns the number of days of a date. To obtain the target day (previous Monday) we will use this DAYS function and will substract the result of step 1 and integer 6.In order to get the date again we will use DATE function...
Step 5
Here is the final result
select DATE(DAYS(MAX(PDATE))-(DAYOFWEEK_ISO(MAX(PDATE)))-6)
AS PRE_MONDAY FROM FINANCE.ACCOUNTS



Salih Sipahi
Software Engineer.
City of Istanbul Turkey
s.sipahi@sahinlerholding.com.tr