0

I have two dates start and end date i need find list of months between two dates.for example, 01-12-2015 as start date and 01-02-2015 as end date i need result to be as , January. I am using IBM db2. another thing i need to find these using select query i have no access to create function or procedures.

  • in any case good to have information about version DB2 (LUW or zOS for ex). But main question - is it strictly unreal create procedure? – a_vlad Nov 19 '15 at 19:17
2

Your date format is ambiguous but given your expected answer is January, I'll assume your start date is 2015-01-02 and end date is 2015-01-12. The following recursive query should do what you need:

WITH months(i, d) AS (
    VALUES (1, DATE('2015-01-02'))

    UNION ALL

    SELECT i + 1, d + 1 MONTH
      FROM months
     WHERE i < 10000
       AND d + 1 MONTH <= DATE('2015-01-12')
)
SELECT monthname(d)
  FROM months

The "i" term in the recursive element is simply there to suppress the "possibly infinite" warning that occurs otherwise. If you're not bothered by the warning, you could just remove it.

This will work on DB2 for LUW and I think it'll work on DB2 for z/OS but that'll depend on whether they've finally added support for VALUES or not; if they haven't just use SELECT foo FROM SYSIBM.SYSDUMMY1 in the base case instead).

-2
case when year(End_Date) = year(Start_Date) 
     then month(End_Date) - month(Start_Date) 
     else month(End_Date) - month(Start_Date) + 12 * (year(End_Date) - year(Start_Date)) 
end Mon,
  • 3
    Can you edit your answer to explain how it addresses the question? Code-only answers can be OK, but answers with more explanation and added value tend to be received better by the community here. – Paul White Reinstate Monica Feb 21 '17 at 6:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.