Everything you wanted to know about dates but never dared to ask, part 1

The Date data type was introduced in V2R3, but it was not until the introduction of RPGLE in V3R1 could we finally make use of it. Over those years I have used the Date data type extensively in my RPG programs and DDS files. I am surprised to still find developers who will not use them, or find others who do not fully understand the functionality of this data type.

Rather than address each item I have found I thought it would be better to write about the Date data type and how I can use them in my RPG code. This will be spread out over several posts, as to cover all I want to do is too much for just one. Let’s start today with what I consider the basics.

Date formats

The date is stored as YYYYMMDD in Date data type variables, fields, and columns. Date formats are just the way the date is presented to us, much like an edit mask or edit code. Some formats can be used when defining Date data types variables, fields, and columns. Then there are others that are only used to move values in or out of Date data types.

These are the formats that are used for defining variables, fields, and columns:

Format name	Description	Layout (with default separator)	Valid separators	Range
*MDY	USA month/day/year	MM/DD/YY	/  -  .  ,  &	01/01/(19)40 – 12/31/(20)39
*DMY	European day/month/year	DD/MM/YY	/  -  .  ,  &	01/01/(19)40 – 31/12/(20)39
*YMD	Year/month/day	YY/MM/DD	/  -  .  ,  &	(19)40/01/01 – (20)39/12/31
*JUL	Julian	YY/DDD	/  -  .  ,  &	(19)40/001 – (20)39/365
*ISO	International Standards Organization	YYYY-MM-DD	-	0001-01-01 – 9999-12-31
*USA	USA standard	MM/DD/YYYY	/	01/01/0001 – 12/31/9999
*EUR	European standard	DD.MM.YYYY	.	01.01.0001 – 31.12.9999
*JIS	Japanese Industrial Standard	YYYY-MM-DD	-	0001-01-01 – 9999-12-31

Yes, *ISO and the *JIS are the same.

If you use "&" as a seperator a space is used.

These are the date formats that can only be used when moving value into or out of Date data types:

Format name	Description	Layout (with default separator)	Valid separators	Range
*CYMD	Century/year/month/day	CYY/MM/DD	/  -  .  ,  &	000/01/01 – 999/12/311
*CMDY	Century/month/day/year	CMM/DD/YY	/  -  .  ,  &	001/01/00 – 912/31/991
*CDMY	Century/day/month/year	CDD/MM/YY	/  -  .  ,  &	001/01/00 – 931/12/991
*LONGJUL	Long Julian	YYYY/DDD	/  -  .  ,  &	0001/001 – 9999/365

¹ The range of dates expressed in conventional written format are January 1 1900 - December 31 2899.

I have seen the *CYMD format most commonly used in older databases, designed in the days when the cost of disk was so expensive it was decided to use only one number for the century to save disk space.

Personally, I think that the *CMMDDYY and *CDDMMYY are ridiculous and I cannot think of a reason to use them.

Defining dates

I use Date data types in RPG, DDS files, and (SQL) DDL tables. Alas, Date data types are not supported in IBM i 7.2 or earlier releases of CL. I am sure they can be used in COBOL, but we don't talk about that programming language in this blog.

In all free RPG defining variables with these different date format could not be simpler as all I have to do is define the variable as a date followed by the format I want to use:

01 dcl-s USA_date date(*usa) ; 02 dcl-s ISO_date date(*iso) ; 03 dcl-s EUR_date date(*eur) ; 04 dcl-s JIS_date date(*jis) ; 05 dcl-s YMD_date date(*ymd) ; 06 dcl-s DMY_date date(*dmy) ; 07 dcl-s MDY_date date(*mdy) ; 08 dcl-s JUL_date date(*jul) ; 09 dcl-s Misc_date date ;

For those of you who still have to use fixed format definitions I would define the variable with a "D" in the data type column and the DATFMT in the keyword column:

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords++++ D USA_date S D datfmt(*usa) D ISO_date S D datfmt(*iso) D EUR_date S D datfmt(*eur) D JIS_date S D datfmt(*jis) D YMD_date S D datfmt(*ymd) D DMY_date S D datfmt(*dmy) D MDY_date S D datfmt(*mdy) D JUL_date S D datfmt(*jul) D Misc_date S D

But wait, for the date on line 9 I have not given a date format. In this case the default date format, *ISO, assumed.

I can change the default date format by using the DATFMT keyword in the Control options/H-specs. In the example below the default date format will be *EUR (DD.MM.YYYY).

In all free RPG:

ctl-opt datfmt(*eur) ;

Or in fixed format.

HKeywords+++++ H datfmt(*eur)

In DDS files, whether a Physical, Display, or Printer file, I define the date in the same way:

A..........T.Name++++++RLen++TDpB......Functions+++ A FLD_DATE L DATFMT(*USA)

To define it as a Date data type field I put "L" in the Data type column. "D" was already taken when the Date data type was introduced, it used in Display files for an alphanumeric fields that can contain only numeric digits. As I am in USA I always use the DATFMT(*USA) so that when the date is displayed in various tools, like Query, it will be shown in *USA format. If I did not use the DATFMT the date would be displayed in *ISO format.

A date column can be coded in a (SQL) DDL table with the following statement:

CREATE TABLE QTEMP/TESTTABLE (DATE_COLUMN DATE NOT NULL )

I have added the NOT NULL as I do not want the date to be null if it contains no value.

Moving values in and out of dates

Between dates

Moving values between Date data types is simple, all I have to do is to move the value in one Date data type variable to another, with one exception. Below is an example of how simple this is, I am using the Date data type variables I showed earlier:

01 dcl-s USA_date date(*usa) inz(*sys) ; 02 dcl-s EUR_date date(*eur) inz(d'2013-06-23') ; 03 YMD_date = %date() ; 04 ISO_date = d'2015-10-30' ; 05 DMY_date = ISO_date ; 06 Misc_date = DMY_date ; 07 JUL_date = Misc_date ; 08 USA_date = d'1939-09-03' ; 09 MDY_date = USA_date ; 10 clear DMY_date ; 11 JIS_date = *loval ; 12 ISO_date = *hival ;

Line 1: I am using the special value *SYS to initialize USA_date with the current system date. For more information on how to use these special values see the post Initializing variables with special values.

Line 2: I am initializing EUR_date with the date 2013-06-23. The "d" indicates that this is a date, followed by the number of the date enclosed in apostrophes ( ' ). Even though I am moving a date into a *EUR date I have to give the date in *ISO format as I have not given the date type in the Control options. If I had then I would have to give the date in that format.

Line 3: This is another way to initialize a date with today's date.

Line 4: ISO_date = 2015-10-30.

Line 5: DMY_date = 30/10/15.

Line 6: As I have not given a date format in the control option Misc_date = 2015-10-30.

Line 7: JUL_date = 15/303.

Line 8: USA_date = 09/03/1939.

Line 9: As the date in USA_date is outside the allowed range for a *MDY formatted Date data type the program errors with the following message:

Message ID . . . . . . : RNQ0114 Severity . . . . . . . : 99 Message type . . . . . : Inquiry Message . . . . : The year portion of a Date or Timestamp value is not in the correct range (C G D F).

Date data types cannot be zero, as it is not a valid date. When I clear DMY_date, line 10, it will contain the lowest date possible in that format, 01/01/40. Another way I can initialize a date to its lowest value is shown on line 11, using *LOVAL initializes JIS_date with 0001-01-01. Now the opposite, if I move *HIVAL to ISO_date I get 9999-12-31, line 12.

Other data types into and out of dates

When moving other data types to Date types I always need to be sure that they contain something that represents a date. If it does not I am going to get an error. Too many times I have been told by other developers "Even though it is a number that field always contain a valid date". Then at 3 AM a program errors as there is a record with "date" value of zero. Make it your rule to always validate the other data type's value before moving it to a Date data type. I did a brief post about this called Validating dates in RPGLE, and I hope with a combination of this post and that one I cover pretty much all aspects of date validation.

We are provided with the TEST(DE) operation for validating any incoming data type is a date. I need to provide the format I wish to validate the incoming value with, and the variable containing the "date". In the example below I am validating that NumericDate contains a value that is compatible with the *USA format, if it does not then the %ERROR is set on:

test(de) *usa NumericDate ; if (%error) ;

Before I give more detailed examples I need to introduce five new variables to our program:

01 dcl-s A_USA char(8) inz('09201995') ; 02 dcl-s A_YMD char(8) inz('93.01.21') ; 03 dcl-s A_ISO1 char(8) inz ; 04 dcl-s A_ISO2 char(10) inz('19501024') ; 05 dcl-s N_CYMD packed(7) inz(1150930) ;

Or in fixed format:

DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++ 01 D A_USA S 8 inz('09201995') 02 D A_YMD S 10 inz('93.01.21') 03 D A_ISO1 S 8 inz 04 D A_ISO2 S 10 inz('19501024') 05 D N_CYMD S 7 0 inz(1150930)

The names of the variables describe the types of "dates" contained within them. Notice that A_ISO1 is blank, therefore, it is not a valid "date". Below are examples of how I would move these "dates" into Data date types of different types:

06 test(de) *usa0 A_USA ; 07 if not(%error) ; 08 YMD_date = %date(A_USA:*usa0) ; 09 endif ; 10 test(de) *ymd. A_YMD ; 11 if not(%error) ; 12 MDY_date = %date(A_YMD:*ymd.) ; 13 endif ; 14 test(de) *iso0 A_ISO1 ; 15 if (%error) ; 16 clear ISO_date ; 17 else ; 18 ISO_date = %date(A_ISO1:*iso0) ; 19 endif ; 20 test(de) *iso0 A_ISO2 ; 21 if not(%error) ; 22 JIS_date = %date(A_ISO2:*iso0) ; 23 endif ; 24 test(de) *cymd N_CYMD ; 25 if not(%error) ; 26 USA_date = %date(N_CYMD:*cymd) ; 27 endif ;

On line 6 I am testing if A_USA contains a *USA formatted "date", as this is an alphanumeric variable I do need to give the separator character used. In this case no separator is used, so I code a zero ( 0 ), which immediately follows the format (*USA0). As the date is valid the error indicator is off, line 7. I can move the value in A_USA using the %DATE built in function into YMD_date. The first parameter is the variable name, the second is the date format for the value in the variable and if alphanumeric variables you also need to give the separator character. In this case there is no separator so zero is used denote there is no separator character. The result is that YMD_date contains 95/09/20.

In the next grouping, lines 10 – 13, I am validating a *YMD formatted date with a period (. ) as the separator. As the "date" is valid means that MDY_date contains 01/21/93.

Line 14 – 19 is the best example as it shows what you should do if the "date" is invalid or valid. As this variable is blank it is going to be invalid. The validation on line 14 checks for a "date" in ISO format with no separator character (*ISO0). As this "date" fails the validation the error indicator is on, line 15. I have chosen to clear the date variable, line 16. There are times when I might want to move another "date" in, for example today's date, instead.

In the group lines 20 – 23 I validate A_ISO2 as an alphanumeric ISO date without a date separator, and then move the value into a *JIS formatted date. The result is that JIS_date contains 1950-10-24.

When validating and moving numeric variables I do not have to worry about a separator character. In the example shown in lines 24 – 27 I am using a numeric representation of a *CYMD formatted date. As I said earlier I cannot define a *CYMD Date data type, but I use this format to move a value from a variable into a Date data type of another format. On lines 24 and 26 there is no separator character with the date format. The result is that USA_date contains 09/30/2015.

To move a value from Date data type into other types I use the %CHAR or %DEC depending on what the result variable is. The examples below take the values in the Date formats above and move them into other types of variables:

28 A_USA = %char(JIS_date:*usa0) ; 29 A_YMD = %char(USA_date:*ymd&) ; 30 A_ISO1 = %char(MDY_date:*iso0) ; 31 A_ISO2 = %char(JIS_date:*cymd-) ; 32 N_CYMD = %dec(YMD_date:*cymd) ;

Line 28: The value in JIS_date is converted to a character data type using the %CHAR built in function. The second parameter in the %CHAR is the format I want to output to the variable A_USA, in this line it is *USA format with no separator. A_USA = '10241950'.

Line 29: The interesting thing about the line is the use of the "&" as the character separator, this denotes that the separator will be a blank. A_YMD = '15 09 30'.

Line 30: With no date separator A_ISO1 = '19930121'.

Line 31: Using the *CYMD with the date separator character results in A_ISO2 = '090-10-24 '.

Line 32: I am using the %DEC built in function with a second parameter of the format I want the result to contain. There is no date separator as I am moving this into a numeric field. N_CYMD = 0950920.

 

Others in this series include:

• Part 2: Calculations using dates, extracting parts of dates
• Part 3: FAQ

 

You can learn more about these BIFs on the IBM website:

• RPG built in function %CHAR
• RPG built in function %DATE
• RPG built in function %DEC
• RPG operation code TEST

 

This article was written for IBM i 7.2, and it should work with earlier releases too.