Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    24

    Question Unanswered: How to get a running date in DB2 UDB 7.2

    Hi,

    What is the fastest way on creating a table with a single column called 'D_date' which contain certain year range .

    If i specify i need a 2004 data, the data in this table wil be someting like this:

    D_date
    2004-01-01, 2004-01-02, ........................................... 2004-12-31


    If i specify i need a 2005 data, the data in this table wil be someting like this:
    D_date
    2005-01-01, 2005-01-02, ........................................... 2005-12-31
    Hello

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    Can you tell us why you need a table like this?

  3. #3
    Join Date
    Mar 2004
    Posts
    24
    to get a calendar table for generating trading day on the stock exchange which exclude saturday, sunday n holiday, so tis table is my based table to achieve this objective.
    Hello

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This recursive SQL should work. You can replace 2005 with a host variable.
    Code:
    WITH temp (da_year) AS
    (VALUES DATE(RTRIM(CHAR(2005)) || '-01-01')
    UNION ALL
    SELECT da_year + 1 DAY
    FROM temp
    WHERE da_year + 1 DAY < DATE(RTRIM(CHAR(2005 + 1)) || '-01-01')
    )
    SELECT *
    FROM temp

Posting Permissions

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