Search code examples
db2db2-luw

how to get Saturday - Friday of prior week in db2

I am looking to get Start date as last to last Saturday and end date as Friday of prior week. So if I run today, start date should be 19th March and end date should be 26th March. Report run date will be every Tuesday.

SELECT (CURRENT DATE-(DAYOFWEEK(CURRENT DATE)-1)DAYS)-9 DAYS,
       (CURRENT DATE-(DAYOFWEEK(CURRENT DATE)-1)DAYS)-2 DAYS 
FROM "SYSIBM".SYSDUMMY1

not sure if its right?

Solution

  • You can easily verify whether your date calculation is correct with a CTE:

    with dates(d) as ( 
    values date('2021-03-01') 
    union all 
    select d + 1 day from dates where d < '2021-05-01'
) 
select d
    , (d - (DAYOFWEEK(d)-1) DAYS)-9 DAYS
    , (d - (DAYOFWEEK(d)-1) DAYS)-2 DAYS 
from dates 
order by d

    It's not exactly an answer to your question, but I'm not 100% sure that I did understand your requirements