get 6 future months from current date

sri.P · #1 (**permalink**) 06-26-09, 08:32

Hi,

I want to get 6 future months for the current month using a query.

Ex: current month is:JUN
output should be: JUL,AUG,SEP,OCT,NOV,DEC

TIA,
Sri

rahul_s80 · #2 (**permalink**) 06-26-09, 08:52

with temp1(no) as (select row_number() over() from syscat.tables fetch first 6 rows only),
temp2(month) as(select current date from sysibm.sysdummy1)
select Substr(monthname(month + no months),1,3) from temp1,temp2

tonkuma · #3 (**permalink**) 06-26-09, 10:33

Another variation on DB2 for LUW may be:

Code:

SELECT Substr(monthname(month + no months),1,3)
  FROM (VALUES current date) AS temp2(month)
     , (VALUES 1,2,3,4,5,6)  AS temp1(no)

Peter.Vanroose · #4 (**permalink**) 06-27-09, 08:40

Quote:

Originally Posted by sri.P

I want to get 6 future months for the current month using a query.

Or with recursive SQL:

Code:

WITH t(d, n) AS
(VALUES (current date + 1 month, 1)
 UNION ALL
 SELECT d + 1 month, n+1
 FROM   t
 WHERE n < 6
),
m(nr,name) AS
(VALUES ((1,'Jan'),(2,'Feb'),(3,'Mar'),(4,'Apr'),(5,'May'),(6,'Jun'),
         (7,'Jul'),(8,'Aug'),(9,'Sep'),(10,'Oct'),(11,'Nov'),(12,'Dec'))
SELECT name
FROM   t INNER JOIN m ON extract(month from d) = nr

tonkuma · #5 (**permalink**) 06-27-09, 11:56

According to the manuals, EXTRACT is available:
DB2 Version 9.1 for z/OS,
DB2 9.7 for Linux, UNIX, and Windows,
DB2 UDB for iSeries V5R3 or later

and for EXTRACT( MONTH FROM {date-expression | timestamp-expression} ):
"The result is identical to the MONTH scalar function."