Micro Focus Studio Enterprise Edition for Windows > Programming > Data Access > Micro Focus Database Connectors Version 2.0 for Windows > Database Connectors Reference > XFD Directives > DATE XFD directive

DATE XFD directive

The DATE XFD directive is used to store a field in the database as a date. Because there's no COBOL syntax that identifies a field as a date, you may want to add this XFD directive to differentiate dates from other numbers, so that they enjoy the properties associated with dates in the RDBMS. To view special options for two-digit dates, see the description of the ACUFH configuration file variable 4GL_2000_CUTOFF.

Syntax

$XFD DATE=date-format-string

This XFD directive implies the NUMERIC XFD directive.

If no date-format-string is specified, six-digit (or six-character) fields are retrieved as YYMMDD from the database. Eight-digit fields are retrieved as YYYYMMDD.

The date-format-string is a description of the desired date format, composed of characters from the following list:

M	Month (01–12)
Y	Year (two or four digits)
D	Day of month (01–31)
J	Julian day (00000000–99999999)
E	Day of year (001–366)
H	Hour (00–23)
N	Minute
S	Second
T	Hundredths of a second

Any other characters cause the date format string to be invalid and result in a corrupt XFD error or a compile-time warning.

Each character in a date format string can be considered a place holder that represents the type of information stored at that location. The characters also determine how many digits are used for each type of data.

For example, although you would typically represent the month with two digits, if you specify "MMM" as part of your date format, the resulting date uses three digits for the month, left-zero-filling the value. If the month is given as "M", the resulting date uses a single digit and truncates on the left.

Julian Dates

Because the definition of Julian day varies, the DATE XFD directive offers a great deal of flexibility for representing Julian dates. Many source books define the Julian day as the day of the year, with January 1st being 001, January 2nd being 002, and so forth. If you want to use this definition for Julian day, simply use "EEE" (day of year) in your date formats.

Other reference books define the Julian day as the number of days since some specific base date in the past. This definition is represented in the DATE XFD directive with the letter "J" (for example, a six-digit date field would be preceded with the XFD directive $XFD DATE=JJJJJJ). The default base date for this form of Julian date is 12/31/4714 BC.

You may define your own base date for Julian date calculations by setting the ACUFH configuration file variable 4GL_JULIAN_BASE_DATE.

Handling Invalid Dates

Dates in the following range are considered valid:

01/01/0001 to 12/31/9999

If the interface considers a date valid and passes it to an RDBMS that considers it invalid, the results depend on the particular RDBMS. Some systems return an error code. Some store the date as NULL and do not return an error. Some store the date in an unpredictable fashion and do not return an error. We recommend that you determine the error handling procedures of the RDBMS in use.

If a COBOL program attempts to write a record containing a date that the Database Connectors knows is invalid, the Database Connectors inserts NULLs into the date field and writes the record.

If a COBOL program attempts to read into a record from a table with a NULL date field, zeroes are inserted into that field in the COBOL record.

For date fields having two-digit years, by default years 0 through 19 are inserted as 2000 through 2019, and years 20 through 99 are inserted as 1920 through 1999. You can change this behavior by changing the value of the variable 4GL_2000_CUTOFF.

Note:

If a field is used as part of a key, the field cannot be a NULL value. For information about converting date values using the 4GL_CONVERT_DATE_ZERO variable, see the topic 4GL_CONVERT_DATE_ZERO.

Using Group Items

You may place the DATE XFD directive in front of a group item, as long as you also use the USE GROUP XFD directive.

Examples

Example 1

 $xfd date
      03  date-hired    pic 9(8).
      03  pay-scale     pic x(3).

The column "date-hired" has eight digits is type DATE in the database, with a format of YYYYMMDD.

Example 2

$XFD DATE, USE GROUP
      03  date-hired.
          05  yyyy      pic 9(4).
          05  mm        pic 9(2).
          05  dd        pic 9(2).

This also produces a column named "date-hired" with eight digits and type DATE in the database, format YYYYMMDD.

Example 3

 $XFD DATE=EEEYYYY
      03  date-sold      pic 9(7).
      03  sales-rep      pic x(30).

This produces a column named "date-sold" with seven digits and type DATE in the database. The date contains the day of the year (for example, February 1st would be 032), followed by the four-digit year, such as 1999).