Is there a system calendar in an iSeries Database?

Ouch. Tricky question. Numerous "calendars" exist, but using them for a task such as the one you need requires programming preparation. For the most part, "calendar" functions are left up to software vendors for implementation of interfaces. If nothing has ever been made available on your system, it might take some effort. Calendar attributes are exposed as interfaces to the virtual machine, so basic access is through APIs at the machine interface (MI) language level.

But it's possible to use almost any language that you have available to do most general date arithmetic operations. I'd need to think a bit to come up with a decent "count Saturdays in any given month" routine.

Is RPG the language you need to use? Do you know if it is ILE RPG? Do you know what OS release the programming must support? Do you have SQL available for development? (SQL exists on all AS/400s, but language compiler interfaces for embedded SQL require an additional product feature.) SQL isn't required, but it might be more familiar for you.

Tom

The simple answer is no - you will need to write one but you can download one here if you have an rpgle compiler

http://www.provatosys.com/Calendar.html

Dave

Hi Anthony,

DB2 SQL has some pretty powerful date calculation features.  It's pretty easy to calculate the first Saturday of the month, number of Saturdays, etc.  If you've got to connect to a DB2 database on the iSeries, perhaps writing a function that returns the count would work for you?


Kent

Hi All, thanks for the prompt responses.  We are using OS/400 and do have access the SQL compiler interfaces.  But we do not use DB2.  

In the time that I was thinking about this, I decided to make my own calculator within a table that I imported from Excel.  Basically it's an Excel spreadsheet that goes from 1/1/1998 to 12/31/2099.  I have created one column that has dates in the format of "mm/dd/yyyy" then I have another column that has the dates in "CYYMMDD" format and then I also have another column that has the actual day of the week.  This was all very easy to do in Excel and I believe that it will work for what I'm trying do to.

Since my background is in SQL and VB, I can make a solution using the structure of this "calendar".

Does anyone have a reason this wouldn't work?

Hi
if you are goint to take this option then I would also put a "None-working-day Flag". This could then be used for other functions later down the line.

That's a great suggestion, but I actually can't add that.  Reason being that people's schedules range from Sunday-Saturday, so technically everyday would be considered a working day.  

But a great idea otherwise.

I was thinking about bank holidays - christmas etc..

They still work- the employees are food retail stores, like supermarkets, a majority of they never close.

Quick point of clarification:

Hi All, thanks for the prompt responses.  We are using OS/400 and do have access the SQL compiler interfaces.  But we do not use DB2.  

If you store data in tables (physical files) on the AS/400, you absolutely do use DB2.  DB2 is built into OS/400, and is a core operating system component.  You access DB2 data on OS/400 through two basic interfaces:  the "native" I/O interfaces (DDS-described files, and traditional READ/WRITE RPG and COBOL file operations), or through SQL.

Just say "no to calendar files

As far as creating a "Calendar file" goes - I've seen several systems that use something similar.  I don't like it.  I think it is a sloppy solution.  Date math just isn't that hard.    I say take a few extra minutes and just do it right.

Calendar files have two major drawbacks as far as I'm concerned:

1) Performance.  From now on, there will be an I/O cost for every program operation you do that needs calendar information.  Date math is pretty easy, and usually much cheaper in terms of performance than designing all your date math around a calendar file.  I've had to rip calendar file logic out of a system - mostly for performance reasons - and it was a pain.

If you decide to go this route, do yourself a favor and at least write procedures to wrap this file, and only use the procedures in your programs.  That way, if performance ever becomes an issue, you can later modify the procedures to do date math instead of file IO to do calendar calculations.

2) Extending the calendar.  I see you built the calendar out to 2099.  That's great, but you've just created a built-in limitation on the useful lifespan of the program, and you've also limited how far back in time you can go.  

This kind of design requires the programmer to either build huge, inefficient calendar files to cover every possible date that could ever be used, design a calendar file extension function, or to accurately predict at the time the routine is designed all possible future cases where it might be used.

As a professional programmer for over two decades, I prefer to write utility code like this (or more likely borrow or adapt someone else's code) once and reuse it over and over in any case I'm likely to encounter.

Consider property record systems that hold dates far in the past, mortgage systems that need to deal with old loans and long-term loans, leasing systems to deal with 99-year leases, government vital statistic systems that might need to track a birthdate from the 1800s, or a program that calculates past and future dates of astronomical events.

A robust calendar system is designed to handle any past or future date.  

Most operating systems and development languages and environments already have excellent date and time math tools built in.

Google "calculate number of saturdays in a month" and you'll find some solutions that at least map out methods that you could use as a starting point for your own code - even if you don't find a complete worked example.

You can solve this particular programming problem in a few lines of code (and no I/O and no calendar file) in most programming languages.  If you can't find an example in Google, post the programming language you plan to use for this and we'll try to help.

One simple routine to do this would iterate over each day in the given month, checking the day of week, and accumulating a counter every time a Saturday is encountered.  Or find the first Saturday in the month, and iteratively add 7 days until you encounter a date in the next month, counting each valid Saturday.  

- Gary Patterson

select CountSaturdays(current date)
  from sysibm.sysdummy1

COUNTSATURDAYS
             4

select CountSaturdays(current date + 1 month)
  from sysibm.sysdummy1

COUNTSATURDAYS
             5

Select allOpen in new window

Gary, thanks for the extensive response- you bring up some great points that I never thought of before.  As I understand the current issue with the limiting the calendar, as I see it right now, the IBM iSeries has an inevitable problem looming.  If you take the standard format of date on the iSeries, "CYYMMDD" what happens when you hit 2100?  Currently you wouldn't be able to distinguish 2000 to 2100.  Just something that came across my mind.  

The calendar system that we are trying to create doesn't go that far back like property taxes.  Our system was implemented in 1/1/1998 and I was able to create the table starting at the date- not saying it's perfect; however, it does solve the problem.  

This billing program that we use will need to incorporate the day name itself as well- it's something that will have to be printed. Again, not to say that it's a perfect way.

Since my programming background falls back to the Microsoft side, it blows my mind to find out that the system doesn't really know what a calendar is or when something falls.  The logic with the calendar file is that I can use it for other programs that don't just try to calculate Saturday's it can also be used for eligibility purposes and things of that nature.  We will try it out first with the logic from the file- if it runs very slow- I'll reach out to you to aid in programming language for this and I will also look to google for help.

Again thanks for the great knowledge, some very good points.  

-Anthony

cyymmdd isn't the standard date format on the iSeries or IBM i.  

That is an old format that was commonly used in the system and in legacy applications in the past.  

Standard dates are stored internally in DB2 in DATE files in Lillian format:

http://en.wikipedia.org/wiki/Lilian_date

DATE format can currently handle dates from 1583 to year 9999, and in future releases could be expanded further.  The discussion gets complicated for dates prior to the adoption of the Gregorian calendar back in 1582 since, since the calendar we use in the US didn't exist before then.

When you request a date from the OS, it is dynamically transformed into any one of a number of different date presentation formats.

Legacy applications sometimes don't store date in DATE fields at all.  Instead, they store them in character or numeric fields in some format defined by the author of the program - a legacy of the olden days before the database supported DATE fields.

I've requested that this question be closed as follows:

Accepted answer: 0 points for Anthony6890's comment #a39539727

for the following reason:

I came up with a solution in the time that it took for someone to get back to me.  Although there were suggestions made after the time, I cannot apply them until I know that the one I've developed will not work.

BTW, the CYYMMDD format uses a century digit of '0' for the 1900s and '1' for the 2000s, mostly. There is potential for IBM using '2' for the 2100s if the basic OS continues to be viable for another 20-30 years.

This format is a 'legacy' format that still has some general uses, but it's not for serious "date" work. The format predates more modern formats, especially those that are appropriate for database dates that need to span many more centuries. In fact, it predates DB2 itself by a couple years.

To avoid limitations of the CYYMMDD format, which is generally designated as "*CYMD" as a format identifier, use a 4-digit year date format. The CYYMMDD format should generally be reserved for use as a command date where the dates are likely to be within a few months of the current date. E.g., it could make sense for selecting all spooled files created on Sept 21st, 2013. But it wouldn't make sense for actuarial tables that look into the future for people born this year.

Tom

No strong objection for myself as long as the chosen 'solution' is sufficiently described and shown to be correct. An imported table might generally work, though it doesn't make much sense since DaveSlash's UDF works and also since many dates are guaranteed not to work if the CYYMMDD format is required in the foreseeable future for dates through 12/31/2099. Many future dates cannot be used validly in CYYMMDD format. E.g., 12/31/2072 cannot be expressed in CYYMMDD as a valid date.

Also, the use of a table sidesteps the question of whether calendars exist or not. The answer to that question is "Yes." Now, the ways to access the calendars gets tricky because of various dependencies. Technically, system calendars are accessed via MI functions. Usually, that's not desirable; so various language constructs are used instead. The definition of 'calendar' is difficult with so many possible calendars in various countries around the world. AS/400 calendars are available for just about every regional variation you can think of because the systems commonly handle international data.

(For a beginning of background on complexities of 'system' calendars, see External Data Formats.)

It wasn't made particularly clear what language constructs were possible after the statement was made that "... we do not use DB2." Often, the use of SQL as a common language can eliminate a lot of possible variations.

For example:

select
  DAYOFWEEK(DATE(year(current date) concat '-'
            concat month(current date) concat '-01')
            + 1 month - 1 day),
  DAYOFMONTH(DATE(year(current date) concat '-'
            concat month(current date) concat '-01')
            + 1 month - 1 day)
from sysibm/sysdummy1

Select allOpen in new window

That SQL statement returns two values. The first is the day-of-week that the last day of the current month falls on, and the second is the number of days in the current month. (Any valid date can be substituted for "current date" in the functions.)

In RPG, the two values could be directly retrieved with a VALUES INTO statement instead of a SELECT statement. A VALUES INTO statement has no necessary connection to "DB2". However, similar results could be generated using only RPG source statements without using SQL. It could be necessary to know what OS versions/releases need to be supported as well as whether or not ILE RPG is allowed. (Some sites restrict it for some usually unspecified reasons.)

The values are useful because any month that has 31 days and that ends on a Saturday, Sunday, Monday or Tuesday will have 5 Saturdays, as will months with 30 days that end on Saturday, Sunday or Monday and months with 29 days that end on Saturday or Sunday. All other combinations have only 4 Saturdays. (Note that the starting day can be used also, but the number of days in the month is always needed.)

The DayName can also be retrieved at the same time, but it's not clear which "day" the name should be for.

Regardless, DaveSlash's UDF in fact solves the problem in the question. And Gary's discussion of drawbacks to a table-based solution addressed the change in the question introduced by the table. (And that's separate from the probable errors introduced by the apparently desired CYYMMDD format.)

Tom

Tom, as you stated in your first response to my question you stated this:

" Tricky question. Numerous "calendars" exist, but using them for a task such as the one you need requires programming preparation. For the most part, "calendar" functions are left up to software vendors for implementation of interfaces. If nothing has ever been made available on your system, it might take some effort."

The solution that I was looking for was a straight calendar without any programming required.  I don't quite understand why you would need programming to develop a calendar- it's really quite a simple process once you develop the table.  Since there was no response to the question the day that I was seeking the help I needed to develop my own way to solve the problem.  The responses didn't come until a day or two after and I had already implemented my calendar without any performance issues at all.  This is why I accepted the solution.  I couldn't verify if the solutions were good for me as I didn't want to reprogram my program for the given solutions.

Wasn't trying to offend anyone by not accepting their ideas.  Had they came sooner, I would have tried them.

Understood. If the other solutions work as well then I don't see an issue at dispersing the points amongst the individuals that did provide answers. The question was left open because of no Internet access over the weekend. My mistaken for not closing it the day I thought about the calendar table.