Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    37

    Unanswered: Calendar table from select

    Hi,
    I have a simple calendar table generated in SqlServer that I need to create in DB2, just a list of months between two dates. The MSS is
    Code:
    -- SQL Server version
    -- Generate a calendar table containing the months from start to end date
    WITH cte_months 
    AS 
    ( 
        SELECT cast('2009-10-01' as datetime) AS aMonth     -- parameter
        UNION ALL 
        SELECT DATEADD(mm, 1, aMonth) 
        FROM cte_months
        WHERE DATEADD(mm, 1, aMonth) < '2010-10-01'        -- parameter
    ) select * from cte_months
    which gives output
    Code:
    2009-10-01 00:00:00.0
    2009-11-01 00:00:00.0
    2009-12-01 00:00:00.0
    2010-01-01 00:00:00.0
    2010-02-01 00:00:00.0
    2010-03-01 00:00:00.0
    2010-04-01 00:00:00.0
    2010-05-01 00:00:00.0
    2010-06-01 00:00:00.0
    2010-07-01 00:00:00.0
    2010-08-01 00:00:00.0
    2010-09-01 00:00:00.0
    Can anyone help changing to DB2?

    thanks

  2. #2
    Join Date
    Jul 2009
    Posts
    150

    Arrow No problem

    Easy:

    Code:
    WITH cte_months (tms)
    AS 
    ( select timestamp(date('2009-10-01'), time('00:00:00'))
         from sysibm.sysdummy1
    union all
    select tms + 1 month
    where 
    tms + 1 month < timestamp(date('2010-10-01'), time('00:00:00')) 
    )
    select tms from cte_months
    Kara

  3. #3
    Join Date
    Nov 2009
    Posts
    37
    Negative: Error: com.ibm.db2.jcc.b.co: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=tms;ms + 1 month
    where
    ;,, DRIVER=3.53.70, SQL State: 42601, Error Code: -104


    I see what youre trying but I cant get it to work

  4. #4
    Join Date
    Nov 2009
    Posts
    37
    You were just missing the alias for tms, its working now, cheers. Next to convert it to Oracle, damn multiple DB support

    Code:
    WITH cte_months (tms)
    AS 
    ( 
        select timestamp(date('2009-10-01'), time('00:00:00')) tms from sysibm.sysdummy1
        union all
        select tms + 1 month from cte_months
        where tms + 1 month < timestamp(date('2010-10-01'), time('00:00:00')) 
    ) select tms from cte_months

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Exclamation

    Quote Originally Posted by FLANDERS View Post
    You were just missing the alias for tms, its working now, cheers. Next to convert it to Oracle, damn multiple DB support

    Code:
    WITH cte_months (tms)
    AS 
    ( 
        select timestamp(date('2009-10-01'), time('00:00:00')) tms from sysibm.sysdummy1
        union all
        select tms + 1 month from cte_months
        where tms + 1 month < timestamp(date('2010-10-01'), time('00:00:00')) 
    ) select tms from cte_months
    You fixed the mistake of DB2Plus !

    Lenny

Posting Permissions

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