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

1. Registered User
Join Date
Jan 2003
Posts
1,636

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. Registered User
Join Date
Feb 2008
Location
Japan
Posts
3,483
You can use recursive query.

3. Registered User
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@

4. Registered User
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
•