IBM has added a great deal of functionality in the area of date and time handling to the RPG language in recent years. These features were added gradually over time and seemingly without a master strategy from IBM. This results in confusion in the RPG community over best practises and a wide variety of solutions for a small number of problems. You may find great variation of coding techniques used to handle date and time issues.
Here are some notes and experiments with recent date features in RPGIV and DDS
Below is an example of a DDS source member with data types DATE TIME and DATESTAMP
A R EMPLOYEE A EMPLOYEE_N 6 0 A NAME_F 25A A NAME_L 25A A BIRTH_DATE L A BIRTH_TIME T A TIMESTAMP Z |
For those who prefer creating their AS/400 physical files with an SQL statement (didn't know you could do this, eh?), it would look something like this:
CREATE TABLE mylib/MYFILE ( EMPLOYEE_NO INT NOT NULL WITH DEFAULT, NAME_F CHAR ( 25) NOT NULL WITH DEFAULT, NAME_L CHAR ( 25) NOT NULL WITH DEFAULT, BIRTH_D DATE NOT NULL WITH DEFAULT, BIRTH_T TIME NOT NULL WITH DEFAULT, TIMESTAMP TIMESTAMP NOT NULL WITH DEFAULT ) |
If you don't populate a date field it will contain the lowest acceptable value of a date field. Different applications may interpret date data differently. If you look at an un-populated record in DFU, it will look like this.
EMPLOYEE_N: NAME_F: NAME_L: BIRTH_DATE: 0001-01-01 BIRTH_TIME: 00.00.00 TIMESTAMP: 0001-01-01-00.00.00.000000 |
Note that SQL/400 is not as informative:
BIRTH_DATE BIRTH_TIME TIMESTAMP ++++++++ 00:00:00 0001-01-01-00.00.00.000000 ******** End of data ******** |
AS/400 date fields are stored internally as a 4 byte integer representing a number
of days from a given base date. This is called the super-julian method by
some. However, whenever the date is to be displayed, printed, written to a
file, etc, the date is converted from an integer to the specified
format. 0001-01-01 is the smallest date you can enter into a date field. If you try to enter 0000-00-00, the
operating system will toss you an error message.
Note that some ODBC drivers have problems with date 0001-01-01.
If you are working in a client server environment, you may have to populate date
fields with 1950-01-01 or something like this to represent an unknown or no date.
The default usage of the date type is *ISO, which has a field length of 10 and is displayed in YYYY-MM-DD format , but you can override that using the DATFMT keyword in the DDS, in the RPG control specification (H-spec) or at the D-spec when defining a date. A program or file can have many dates defined, all with different formats.
A DATE1 L DATFMT(*MDY) A DATE2 L DATFMT(*MDY) A DATE3 L DATFMT(*DMY) A DATE4 L DATFMT(*YMD) A DATE5 L DATFMT(*JUL) A DATE6 L DATFMT(*ISO) A DATE7 L DATFMT(*USA) A DATE8 L DATFMT(*EUR) A DATE9 L DATFMT(*JIS) |
If you look at the fields defined above, in an unpopulated record in DFU, it will show the values below. Notice the different default values and separators used. Note that the date formats that display the year in a two digit format (like *YMD and *JUL) have a valid range of January 1, 1940 through December 31, 2039.
DATE1: 01/01/40 DATE2: 01/01/40 DATE3: 01/01/40 DATE4: 40/01/01 DATE5: 40/001 DATE6: 0001-01-01 DATE7: 01/01/0001 DATE8: 01.01.0001 DATE9: 0001-01-01 |
You have a similar range of choices for the new Time data type. The default here is also *ISO
A TIME0 T A TIME1 T TIMFMT(*HMS) A TIME2 T TIMFMT(*ISO) A TIME3 T TIMFMT(*USA) A TIME4 T TIMFMT(*EUR) |
Here I wanted to enter 23:50:59 in each field. The results are pretty predictable.
TIME0: 23.50.59 TIME1: 23:50:59 TIME2: 23.50.59 TIME3: 11:50 PM TIME4: 23.50.59 |
Here is an example of defining date, time and timestamp fields in an RPG program.
d* Date1 defaults to *ISO '0001-01-01' d Date1 s d d* d* Date2 set to initialize with type *USA '01/01/0001' d Date2 s d DATFMT(*USA) d* d* Time1 initialize to value '00.00.00' d Time1 s t d* d* TimeStamp1 initialize to value '0001-01-01-00.00.00.000000' d TimeStamp1 s z d* |
Here is an example of a data structure that allows you to break a date-type field into YYYY MM DD numeric fields. These are very common in older programs:
d ds Inz d Date1 1 10d DatFmt(*ISO) d Year 1 4 0 d Month 6 7 0 d Day 9 10 0 |
In current versions of RPG, the EXTRCT (Extract Date) keyword makes this task simpler and more explicit.
dDate1 s d Inz(d'2002-01-30') C Extrct Date1:*Y Year 4 0 C Extrct Date1:*M Month 2 0 C Extrct Date1:*D Day 2 0 |
The new versions of RPG offer a range of new tools for date mathematics.
dDate_From s D INZ(D'2004-01-31') dDate_To s D dTime_From s T INZ(T'01.00.00') dTime_To s T c c* 30 days from 2004-01-31 takes us to 2004-03-01 c ADDDUR 30:*D Date_from c* backup 30 days backwards takes us back to 2004-01-01 c ADDDUR -30:*Days Date_from c* 30 days from 2004-01-31 takes us to 2004-01-31 c Date_from ADDDUR 30:*Days Date_To c* 30 minutes from 01.00.00 takes us to 01.30.00 c Time_from ADDDUR 30:*Minutes Time_To c Eval *inLR = *on |
dDATE_from s D INZ(D'2002-01-01') dDATE_to s D INZ(D'2002-01-02') dDays s 4 0 c Date_to SUBDUR Date_from days:*D |
d ToDate s D INZ(D'2004-01-02') d FromDate s D INZ(D'2003-01-02') d NextMonth s D d NextYear s D d Days s 4 0 /free // add 30 days Eval NextMonth = ToDate + %days(30) ; // add 6 months Eval NextYear = ToDate + %months(12) ; // calculate number of days between 2 dates Eval Days = %diff(ToDate : FromDate: *d ) ; Eval *inlr = *on ; /end-free |
EXTRCT: To extract Year/Month/Day information from a date field
dDATE1 s D INZ(D'2002-01-02') dYear s 4 0 dMonth s 2 0 dDay s 2 0 C EXTRCT Date1:*Y Year C EXTRCT Date1:*M Month C EXTRCT Date1:*D Day |
In this example we get a numeric value of a date using the %subdt function.
*--------------------------------------------------------------------- dDate_N s 8 0 dDate_D s D INZ(d'2002-12-31') *--------------------------------------------------------------------- /free // get a numeric value of a date using the %subdt function // example: extract numeric 20021231 from date '2002-12-31' Eval Date_N = (%subdt(Date_D:*y) * 10000) + (%subdt(Date_D:*m) * 100) + (%subdt(Date_D:*d)) ; Eval *InLR = *On ; /end-free
Because the date data type seems similar to a character data type, you may be
tempted to treat it like a string. This is not always a good idea, as
following substring example illustrates:
dDate1 s d Inz(d'2002-01-30') dDateString s 10 Inz( '2002-01-30') dYearString s 10 dYearNumeric s 4 0 C* This will work because the substring function will operate on a string C Eval YearString = %subst(DateString:1:4) C* C* This will NOT work because the substring function will NOT operate on a date field C Eval YearNumeric = %subst(Date1:1:4) |
You can convert alphanumeric or numeric fields to type Date. This is useful when bridging old code to your new files or when importing external data sources. Before such a conversion, it would be strongly advised that the numeric or character data be tested to ensure that the data represents a valid date before converting it to a type Date.
The TEST(D) OpCode allows you to test the validity of date. This following test of a numeric value will test successful (*IN50 will be = *off) because 12/31/02 is a valid date of type MM/DD/YY.
dDateNumeric s 6 0 c eval DateNumeric = 123102 c *mdy test(d) DateNumeric 50 |
Here an eight character string containing '12/31/02' will test successfully.
dDateString s 8 c eval DateString = '12/31/02' c *mdy test(d) DateString 51 |
Get the system date and assign its value to a type Date field. In one case the system date is assigned to a numeric field, then to a date field. In the second case, we assign the today's date to the date field Date2,
hDatedit(*ymd) d Date_N8 S 8 0 d Date1 S D d Date2 S D c* Retrieve current date, move to numeric field then to date field c Eval Date_N8 = *Date c Move Date_N8 Date1 c* Retrieve current date, move directly to date field c Move *date Date2 |
using the RPG /free feature we can assign a dates even more easily and explicitly.
d today S D /free // set today to today's date. today = %date(*date) ; *inlr = *on ; /end-free |
It is also possible to use the %date function to assign a string containing a valid date to a date field.
d mydate s d d mystring s 10 /free eval mystring = '2001-01-01' ; eval mydate = %date(mystring) ; eval *inlr = *on ; /end-free |
Here is an example where we manually assemble a timestamp field.
DTimeStampStr DS D Stamp 26 Inz('0000-00-00-00.00.00.000000') D Century 2 OverLay(Stamp:1) D Year 2 OverLay(Stamp:3) D Month 2 OverLay(Stamp:6) D Day 2 OverLay(Stamp:9) D Hour 2 OverLay(Stamp:12) D Minute 2 OverLay(Stamp:15) D Second 2 OverLay(Stamp:18) D MSecond 6 OverLay(Stamp:21) D TimeStampTS S Z c Eval Century = '20' c Eval Year = '04' c Eval Month = '02' c Eval Day = '13' c Eval Hour = '01' c Eval Minute = '59' c Eval Second = '30' c Eval MSecond = '000000' c Move TimeStampStr TimeStampTS c Seton LR |