0

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

Fred Sobotka
  • 5,252
  • 22
  • 32
user3593083
  • 100
  • 1
  • 10
  • show us what you have tried. – danny117 Jan 20 '15 at 18:32
  • Tried: the comment from marc_s at http://stackoverflow.com/questions/4737378/generate-sql-temp-table-of-sequential-dates-to-left-outer-join-to and changed the date formats to what I needed. No luck... my visual query builder didn't like the post from Gilm at http://stackoverflow.com/questions/7812986/easiest-way-to-populate-a-temp-table-with-dates-between-and-including-2-date-par as it appears it doen'st work well with CTE. – user3593083 Jan 20 '15 at 18:55
  • 1
    Then stop using the tool that prevents you from doing what you need to do (instead of helping). You should also mention the version of your IBM i system. – mustaccio Jan 21 '15 at 00:04
  • 1
    You're going to want an actual `DATE` column, plus a whole bunch of other stuff (like extracting year, month, and day-of-month). A prime benefit of a date table _is_ the other columns, which make certain types of analysis really easy. – Clockwork-Muse Jan 21 '15 at 12:19

3 Answers3

2

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

Fred Sobotka
  • 5,252
  • 22
  • 32
  • Certain CTEs do not work in my visual query builder. I am getting errors at the "Values" statement in your link. I believe the dates are integers and not actually dates. – user3593083 Jan 20 '15 at 19:13
  • The link is only an example of how a recursive CTE can be used as a looping structure to generate a range of values. You will need to adjust it to suit your needs. In the case of a date generator, you'd be better off generating the values in the CTE as actual dates first, then casting them to integers in the final/outermost SELECT that consumes the results out of that CTE. – Fred Sobotka Jan 20 '15 at 19:40
  • 1
    If DB2 for i doesn't like a `VALUES` expression as the seed for a recursive CTE, you could also `SELECT` the seed value from SYSIBM.SYSDUMMY1. – Fred Sobotka Jan 20 '15 at 19:46
2

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.

Buck Calabro
  • 7,558
  • 22
  • 25
  • Most efficient answer of the three given, and clearly the easiest. An elegant example of the power of recursive CTEs. – WarrenT Jan 27 '15 at 17:24
  • I even tried changing your CASE expression to use a [simple-when-clause](http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzcaseexp.htm) as `CASE substr(char(dt),2,1) WHEN '0' THEN '1' ...` but that only slowed it down. – WarrenT Jan 27 '15 at 17:33
1

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.

Charles
  • 21,637
  • 1
  • 20
  • 44