How to get a running date in DB2 UDB 7.2

act7656 · #1 (**permalink**) 02-28-05, 07:00

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

urquel · #2 (**permalink**) 02-28-05, 09:23

Can you tell us why you need a table like this?

act7656 · #3 (**permalink**) 02-28-05, 10:04

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.

Wim · #4 (**permalink**) 02-28-05, 10:10

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

Internet Services

Web Development

Digital Marketing

Consumer Tech