How to get the date of the upcoming January?

Question

I am using DB2 and writing an SQL query on a table where I have one column for each upcoming month. Like, Jan, Feb, Mar, Apr, May etc. 12 in total.

The data is forecasted sales for the upcoming 12 months. It is recalculated every night.

For example, if the current month is November 2019, the November-column contains the forecast for this November 2019, the December-column for December 2019 etc. But, the January-column has data for January 2020.

How can I, in SQL, get the date of the upcoming month that the column represent?

I need a function like GetNextJanuary(), which returns 20200101 if used this year, and GetNextDecember() which returns 20191201 etc. However, if I would use GetNextJune() today, it should return 20190601 (the current month).

Is this possible using SQL alone?

Answer 1

We may phrase this problem as adding one year to the input date, then truncating to the start of that year:

SELECT
    DATE_TRUNC('YEAR', ADD_YEARS(CURRENT_DATE, 1))
FROM dual;

Answer 2

You can use case logic:

select (case when month(current date) <= 1
             then to_date(year(current date) || '01-01', 'YYYY-MM-DD')
             else to_date(year(current date) + 1 || '01-01', 'YYYY-MM-DD')
        end) as jan_start_of_month,
       (case when month(current date) <= 2
             then to_date(year(current date) || '02-01', 'YYYY-MM-DD')
             else to_date(year(current date) + 1 || '02-01', 'YYYY-MM-DD')
        end) as feb_start_of_month,
       . . .

Answer 3

create or replace function GetNextMonth(p_month int)
returns date
deterministic
no external action
return 
  date(digits(dec(year(current date), 4))||'-'||digits(dec(p_month, 2))||'-01')
+ (case when p_month < month(current date) then 1 else 0 end) years;

select *
from table(values 
(getnextmonth(1), getnextmonth(12), getnextmonth(6))
) t (jan, dec, june);

JAN        DEC        JUNE
---------- ---------- ----------
2020-01-01 2019-12-01 2019-06-01

The logic is: if the month number passed (p_month) is less than current month, then we add 1 year to the date constructed from the 1-st day of the current year of the month number passed, and 0 years otherwise.