How to display list of months between two dates?

grofaty · #1 (**permalink**) 09-08-09, 02:05

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

tonkuma · #2 (**permalink**) 09-08-09, 02:59

You can use recursive query.

sunil.tcs · #3 (**permalink**) 09-08-09, 03:52

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..

tonkuma · #4 (**permalink**) 09-08-09, 04:09

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)
)

Internet Services

Web Development

Digital Marketing

Consumer Tech