Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    2

    Unanswered: get 6 future months from current date

    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

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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)
    Last edited by tonkuma; 06-26-09 at 11:38.

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    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
    Last edited by Peter.Vanroose; 06-27-09 at 11:47.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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."

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by tonkuma
    According to the manuals, EXTRACT is available:
    "The result is identical to the MONTH scalar function."
    Actually, EXTRACT(... from ...) is the SQL ANSI/ISO standard function.
    Strictly speaking, the month() function is a DB2 (and SQLServer) dialect.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Peter Vanroose, you are right.
    But, many people who can't access latest version of DB2 need to use DB2 dialect(MONTH or MONTHNAME).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •