CODEWITHSUNDEEP
sqldb2ibm-midrangedb2-400
user3593083
user3593083

Reputation: 100

Create table of dates in iSeries (OS400)

Morning all!

Trying to create a table of dates for my iSeries. Need only a column with a row for each date starting at 1/1/2014 and going until 12/31/2115 so I have about a hundred more years.

I would like to use SQL and have the date in CYYDDMM format. I've tried to modify several posts from here and some other sites but had no luck. It appears the SQL required to get what I need is past my limited knowledge.

The table has been created with one column (shift_date) that is an integer. Any assistance is greatly appreciated!

Thanks in advance!

Matt

Upvotes: 0

Views: 1599

Answers (3)

Buck Calabro
Buck Calabro

Reputation: 7648

Based on the links provided by @Fred and @Charles, here is an SQL statement that worked on my V7.2 machine:

WITH ALL_DAYS(DT) AS
  ( VALUES (DATE('2014-01-01'))
      UNION ALL
    SELECT DT + 1 DAY FROM ALL_DAYS
    WHERE  DT < '2115-12-31'
  )
SELECT DT, 
 cast(case 
    when substr(char(dt), 2, 1) ='0' then '1' 
    when substr(char(dt), 2, 1) ='1' then '2' 
    when substr(char(dt), 2, 1) ='2' then '3' 
  end concat
  substr(char(dt), 3, 2) concat
  substr(char(dt), 6, 2) concat
  substr(char(dt), 9, 2) as decimal(7, 0)) as cyymmdd 
FROM ALL_DAYS;

You don't need to execute this from within a Microsoft tool. IBM i comes with several tools you can use (STRSQL on the green screen and IBM i Navigator on the PC) as well as 3rd party tools like SQuirreL. Basically, anything that will directly execute an SQL statement will work, including writing your own .NET program. You could even have the RPG programmers embed this into a program and run it from there.

Upvotes: 2

Fred Sobotka
Fred Sobotka

Reputation: 5332

DB2 and many other DBMSes allow recursive SQL through common table expressions (CTEs).

Upvotes: 2

Charles
Charles

Reputation: 23783

In the "Getting Started with DB2 Web Query for i" Redbook found here:
http://www.redbooks.ibm.com/abstracts/sg247214.html

Appendix B - Date and time functionality - has a section, "Using date conversion table to convert to dates". That talks about creating, populating and using a date conversion table. It also includes the SQL statements you can use to create and populate such a table.

Alternatively, the REDBOOK page linked above has a link "Additional Materials" from which you can download the discussed date conversion table/statements as save files.

Upvotes: 1

Related Questions