Make my day

Sometimes you need things to be done on the beginning or the middle of the month. Fees need to be payed, invoices to be sent. A database could hold the next day when the task should be performed. But how to calculate it in SQL if the result needs to be the 1st or 16th?

So lets start playing with days. This little code snippet results in the first day of the current month:

select
         current_date + 1 day
 - ( day(current_date) )  days
from sysibm.sysdummy1;

Subtract the number of the current day and add one. So in Feb. 2008 it would bring up the date expression '2008-02-01'. Now we need a code which gives us the delta of zero or 15 days, depening on the current day and if it's in the lower or the upper part of the month:

select
  ( (day(current_date) / 16 ) * 15 ) days
from sysibm.sysdummy1;

Due to that day(current_date) is maximum 31, the integer division by 16 will result in zero or one. Multiplied with 15, this is the delta we need. So together these codes snippets will give us the first or 16th of the month:

select
           current_date   + 1 day
 -  (  day(current_date) ) days
 +  ( (day(current_date) / 16 ) * 15 ) days
from sysibm.sysdummy1 ;

It can be varied for values of 16 and above, to provide e.g. the 20th or the 1st day of the current month. Remember to divide by x and then multiply by (x-1) in the last addend. Values of 15 and lower won't work.