Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to display list of months between two dates?

    Hi,
    I would like to get list of all months that are between two dates. For example if I enter '2009-11-01' as minimal date and
    '2010-03-15' as maximum date, I would like to get display years and months between two dates like:

    Code:
    YEAR        MONTH
    ----------- -----------
           2009          11
           2009          12
           2010           1
           2010           2
           2010           3
    I have written the following SQL that returns exactly the same output as desired. The problem with the following SQL is that
    there are constant values between 0 and 30, that is SQL part in VALUES (0), (1) etc, so I have only 31 months interval available.
    I know I can set this interval from 0 to 1000 for example, but this SQL would still have limitations if interval between two dates
    is more then 1001 months. Is there any way I could write such a SQL that would return data without this interval limitation?

    Code:
    WITH TEMP (NUMBER) AS
    (
     VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9), (10),
     (11), (12), (13), (14), (15), (16), (17), (18), (19), (20),
     (21), (22), (23), (24), (25), (26), (27), (28), (29), (30)
    )
    (
    SELECT
    YEAR  (DATE('2009-11-01') + NUMBER MONTHS) AS YEAR,
    MONTH (DATE('2009-11-01') + NUMBER MONTHS) AS MONTH
    FROM
    TEMP
    WHERE
    YEAR(DATE('2009-11-01')+NUMBER MONTH)*100+MONTH(DATE('2009-11-01')+NUMBER MONTH)
    BETWEEN YEAR(DATE('2009-11-01'))*100+MONTH(DATE('2009-11-01'))
    AND
    YEAR(DATE('2010-03-15'))* 100 + MONTH(DATE('2010-03-15'))
    )
    Regards
    Last edited by grofaty; 09-08-09 at 06:10.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can use recursive query.

  3. #3
    Join Date
    Aug 2009
    Posts
    12
    CREATE FUNCTION DBO.months_between(d1 TIMESTAMP, d2 TIMESTAMP) RETURNS
    FLOAT
    F1: BEGIN ATOMIC
    RETURN 12 * (year(d1) - year(d2)) + month(d1)- month(d2) + ( TIMESTAMPDIFF(2, CHAR(d1 - (d2 + (12*(year(d1) - year(d2)) + month(d1) - month(d2)) MONTHS))) / 2678400.0 );--
    END@


    please try above.. hope it is helpful..

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Maximum year in DB2 is 9999.
    So, 9999 * 12 interval will be enough.
    You can construct TEMP table like this:
    Code:
    WITH TEMP (NUMBER) AS (
    SELECT n1 + n2 * 10 + n3 * 100 + n4 * 1000 + n5 * 10000 + n6 * 100000
      FROM (VALUES 0,1,2,3,4,5,6,7,8,9) n1(n1)
         , (VALUES 0,1,2,3,4,5,6,7,8,9) n2(n2)
         , (VALUES 0,1,2,3,4,5,6,7,8,9) n3(n3)
         , (VALUES 0,1,2,3,4,5,6,7,8,9) n4(n4)
         , (VALUES 0,1,2,3,4,5,6,7,8,9) n5(n5)
         , (VALUES 0,1)                 n6(n6)
    )

Posting Permissions

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