The Afro Coder

iSeries DB2 SQL First Day of the Week

On the iSeries we will use the sysibm/sysdummy1 file to perform the example calculation.

In my experience sometimes I needed to know the first day of the week based on a current date.

Most of the time the week would start on either Sunday or Monday (never on a Friday!)

Here is a select statement to get the current date

select
      current date
from
      sysibm/sysdummy1

That gives me today’s date

today-date

I can also get the day of the week represented as a number

select
      dayofweek(current date)
from
      sysibm/sysdummy1

today-date

This give me a 5 (I’m writing this on a Thursday)

So is Monday is the beginning of the week (Monday = 2) and today is Thursday (5) I need to do some substraction.

select
 current date - (dayofweek(current date) - 2) days 
from
 sysibm/sysdummy1

We see 4-27 is a Monday and the start of the week

today-date

We can test this by using the dates from the prior week

Here is an example using a temp table and some hardcoded dates.

with dates as
(                                               
 select date('2020-04-19') as lastsun,          
        date('2020-04-21') as lastmon,          
        date('2020-04-21') as lasttue,          
        date('2020-04-22') as lastwed,          
        date('2020-04-23') as lastthu,          
        date('2020-04-24') as lastfri,          
        date('2020-04-25') as lastsat           
        from sysibm/sysdummy1                   
)                                               
select
        lastsun - (dayofweek(lastsun) - 2) days,
        lastmon - (dayofweek(lastmon) - 2) days, 
        lasttue - (dayofweek(lasttue) - 2) days, 
        lastwed - (dayofweek(lastwed) - 2) days,
        lastthu - (dayofweek(lastthu) - 2) days, 
        lastfri - (dayofweek(lastfri) - 2) days, 
        lastsat - (dayofweek(lastsat) - 2) days  
from dates

Each result give the value of 4-20 which is the Monday of that week.

today-date

To start the week with a Sunday then substract 1 day instead of 2 in the calculation.

with dates as
(
 select date('2020-04-19') as lastsun,
        date('2020-04-21') as lastmon,
        date('2020-04-21') as lasttue,
        date('2020-04-22') as lastwed,
        date('2020-04-23') as lastthu,
        date('2020-04-24') as lastfri,
        date('2020-04-25') as lastsat
        from sysibm/sysdummy1
)
select
        lastsun - (dayofweek(lastsun) - 1) days,
        lastmon - (dayofweek(lastmon) - 1) days,
        lasttue - (dayofweek(lasttue) - 1) days,
        lastwed - (dayofweek(lastwed) - 1) days,
        lastthu - (dayofweek(lastthu) - 1) days,
        lastfri - (dayofweek(lastfri) - 1) days,
        lastsat - (dayofweek(lastsat) - 1) days
from dates

Now we get 4-19 instead of 4-20 which is a Sunday.

today-date