You're working on a program written in RPG IV when you notice the date of 01-01-0001. "Wait a minute," you say. "How did that get there when I didn't enter anything?" The answer lies in the requirements of DB2 and the function of date data types.
From the time I began writing code on what is now the iSeries, I have always used date data types. The person whom I learned RPG/DB2 programming from is a proponent of date data types, and I soon became one myself. Storing data by their type just makes sense to me. During my consulting years I did work in shops that stored dates in numeric type.
The questions start when you want to display, edit or print the value in a date field that has no meaningful date. That is where the "01-01-0001" date normally comes in. Remember that we are using DB2 as our database. If the field is defined in DB2 as a date field, then a valid date is required. If you use the UPDDTA command over a file that has a date field and place it in "ADD" mode, you will notice that a default date is set. The format is set by the DB2 definition.
When I write screen and printer fields, I prefer to reference the database field directly and keep the same names to reduce code and confusion. The exception to this rule is dates. Date data types come in all sorts of sizes and formats. From a database design I may want to store the date and time as a timestamp, but I would never ask a user to enter or edit that format on a screen.
In the U.S., people are accustomed to a *MDY format. For that reason I always ask for a date in that fashion. Some users also want to enter the date with the slash (07/01/03) separator and others only want to enter the numbers (070103). In addition, if I place a screen to the user with a new record and the screen field is defined as a date, I will get a value of "01-01-0001". If I print a record and a date field is defined, I cannot clear the date at the time of printing. Again I will get the "01-01-0001" value. This often leads to confusion for the user.
For those reasons, whenever I want to display, edit or print a date field, I use a work field in its place. That way if a date with no value is displayed, I can test for the default value and "clear" the field. Also, the user can enter the date with or without separators, and I can deal with that inside the program. It makes the screens and report look "cleaner" without impacting the design of the database.
So, why do you get 01-01-0001 when you have not entered anything? It's due to the fact that DB2 requires that a date field hold a valid date. There are countless ways to display and manipulate dates and times outside their DB2 formats, but I will leave that for another time.
-----------------------------------------
About the author: John Kohan is a senior programmer analyst at CT Codeworks.
==================================
MORE INFORMATION
==================================
- Using date data-type fields in RPG IV
Date data-types are incredibly powerful and flexible, but figuring out how to use them to do date format conversions can be frustrating. The key to understanding them: the date data-type field is in ALL formats.
- Working with date and timestamp in DB2
Is it possible for DB2/400 to automatically keep track of the timestamp when the record is inserted or updated? Site expert Kent Milligan explains.
- Using RPG/400, convert a date from MMDDYY to YYMMDD
Is there an easy way to convert a date in MMDDYY format to a date in YYMMDD format? Yes, says site expert Laird Scranton, and he provides a formula.
