1st business day in month

Quiet often events are logged in databases for the purpose of statistics or billing. Maybe there's the need to know which day of the month was the first business day. Here's a way to find out with DB2.

Imagine a table log.events, containing a column named when of type date. Basically you'll rely on the function DayOfWeek(), which returns a minimum of 1 in case the given date is sunday, and a maximum of 7 for saturday:

select
  ( 15 - ( DayOfWeek( when - (Day(when) - 1) days) ) ) / 7
  +
  ( 2 * ( DayOfWeek( when - (Day(when) - 1) days) / 7 ) )
from
  log.events ;

With some arithmetic trickery the following code returns

• 3 (1st of month is saturday, 1st business day is monday 3rd)
  
• 2 (1st of month is sunday, 1st business day is monday 2rd)
  
• 1 (1st of month and 1st business day is within tuesday to friday)