System i: Date, time, and timestamp fields using FMTOPT(*MAP) or FMTOPT(*NOCHK)

Date, time, and timestamp fields using FMTOPT(*MAP) or FMTOPT(*NOCHK)

You can use FMTOPT(*MAP) or FMTOPT(*NOCHK) for the date, time, and timestamp fields. The copy operation is handled differently according to your settings.
You must specify FMTOPT(*MAP) or FMTOPT(*NOCHK) on the CPYF command in any of the following cases:

The from-file is a database data file.

The to-file is a physical data file.

The record formats are not identical.

Corresponding date, time, and timestamp fields in the from-file and to-file must have the same format attribute and separator for the record formats to be identical. For the CPYFRMQRYF command, the same is true except that the open query file record format is used (rather than a from-file format).
When using FMTOPT(*NOCHK), record data is copied directly from left to right into the to-file without any regard to field types.
When using FMTOPT(*CVTSRC), data portions of records are directly copied from left to right into the to-file without any regard to the field types.
When using FMTOPT(*DROP), fields in the from-file but not in the to-file are dropped. If any like-named fields in the from-file and to-file are date, time, or timestamp fields, the corresponding field must be the same type, have the same format attribute and separator, and have the same relative position in the record format as the like-named field, otherwise FMTOPT(*MAP) might also be required.
FMTOPT(*MAP) allows copying between like date, time, and timestamp field types regardless of the format or separator. Also, copies from and to date, time, and timestamp fields are allowed from and to zoned-decimal or character field types, provided the lengths, formats, and values can be converted. FMTOPT(*MAP) is required in this case for conversion to the to-field type (format and separator, if it applies).
Table 1 outlines the conversion possibilities for the date, time, and timestamp.

Table 1. Conversion Table
Data types Forms Allowable field length Direction Data type Formats Allowable field length

Date Any date format 6, 8, or 10 <--> Date Any 6, 8, or 10
Zoned (MMDDYY) 6,0 <--> Date Any 6, 8, or 10
Zoned (DDMMYY) 6,0 <--> Date Any 6, 8, or 10
Zoned (YYMMDD) 6,0 <--> Date Any 6, 8, or 10
Zoned (YYDDD) 5,0 <--> Date Any 6, 8, or 10
Character (MMdDDdYY) 6 min <--> Date Any 6, 8, or 10
Character (DDdMMdYY) 6 min <--> Date Any 6, 8, or 10
Character (YYdMMdDD) 6 min <--> Date Any 6, 8, or 10
Character (YYdDDD) 6 min <--> Date Any 6, 8, or 10
Character (*USA) 6 min ----> Date Any 6, 8, or 10
Character (*ISO) 6 min ----> Date Any 6, 8, or 10
Character (*EUR) 6 min ----> Date Any 6, 8, or 10
Character (*JIS) 6 min ----> Date Any 6, 8, or 10
Character (YYYYDDD) 6 min ----> Date Any 6, 8, or 10
Time Any time format 8 <--> Time Any 8
Zoned (HHMMSS) 6,0 <--> Time Any 8
Character (HHtMMtSS) 4 min ----> Time Any 8
Character (*USA) 4 min ----> Time Any 8
Character (*ISO) 4 min ----> Time Any 8
Character (*EUR) 4 min ----> Time Any 8
Character (*JIS) 4 min ----> Time Any 8
Character (HHtMMtSS) 8 min <---- Time Any 8
Timestamp SAA^® format 26 <--> Timestamp SAA 26
Zoned (YYYYMMDDHHMMSS) 14,0 <--> Timestamp SAA 26
Character SAA format 14 min ----> Timestamp SAA 26
Character (YYYYMMDDHHMMSS) 14 min <--> Timestamp SAA 26

In the format columns,

d

= date separator value

t

= time separator value

any

= job formats or SAA formats

In the allowable field-length column, min means the specified length is the minimum required for a conversion attempt. Conversion errors might still occur if the length is not long enough for the assumed format that you want. Refer to the DDS concepts for more information about the date, time, and timestamp data types and keywords.

When converting a character field to a date, time, or timestamp field: The FMTOPT(*MAP) is specified and the corresponding from-field and to-field names match, an attempt is made to determine what similar date form the character field is in. The following rules apply:

Converting a character field to a date field: The minimum length required for the character field is 6. The system first determines if the character field data is in the same format and has the same separator as specified in the current job under which the copy command is running. This might be *MDY, *DMY, *YMD, or *JUL for the format and slash (/), hyphen (-), period (.), comma (,), or blank ( ) for the separator. If the character field is not in the specified format and separator form of the current job, it determines if it is in one of the SAA formats (*ISO, *USA, *EUR, or *JIS), or if it is in a YYYYDDD form (no separator). If the system determines the character field is in one of these forms, the system converts the character field to the date to-field. The date portion of the character field must be left-aligned and can contain trailing blanks.

Converting a character field to a time field: The minimum length required for the character field is 4. The system first determines if the character field data is in the same format and has the same separator as specified in the current job under which the copy command is running. This might be *HMS for the format and colon (:), comma (,), period (.), or blank ( ) for the separator. If the character field is not in the specified format and separator form of the current job, the system determines if it is in one of the SAA formats (*ISO, *USA, *EUR, or *JIS). If the system determines the character field is in one of these forms, it converts it to the time to-field. The time portion of the character field must be left-aligned and can contain trailing blanks.

Converting a character field to a timestamp field: The minimum length required for the character field is 14. The system first determines if the character field data is in one of the following types:

SAA format

YYYYMMDDHHMMSS form
If the system determines the character field is in one of these forms, it converts it to the timestamp to-field. The timestamp portion of the character field must be left-aligned and can contain trailing blanks.

When converting a date, time, or timestamp field to a character field: The FMTOPT(*MAP) is specified and the corresponding from and to-file field names match, the system attempts to convert the date, time, or timestamp field into the form specified by the current job. The following rules apply:

For converting a date field to a character field: The minimum length required for the character field is 6. The system first determines the date format and separator attribute of the current job under which the copy command is running. This might be *MDY, *DMY, *YMD, or *JUL for the format and slash (/), hyphen (-), period (.), comma (,), or blank ( ) for the separator. The date field is converted into the character field in the specified format of the current job. For character fields that are longer than required for the conversion, the data is left-aligned and trailing blanks are added.

For converting a time field to a character field: The minimum length required for the character field is 8. The system first determines the time separator attribute of the current job under which the copy command is running. This might be colon (:), comma (,), period (.), or blank ( ). The time field is converted into the character field in the *HMS format (including the specified separator of the current job). For character fields that are longer than required for the conversion, the data is left-aligned and trailing blanks are added.

For converting a timestamp field to a character field: The minimum length required for the character field is 14. The timestamp field is converted into the character field in the YYYYMMDDHHMMSS form (no separators). For character fields that are longer than required for the conversion, the data is left-aligned and trailing blanks are added.

When converting a zoned decimal field to a date, time, or timestamp field: The FMTOPT(*MAP) is specified and the corresponding from-field and to-field names match, the system assumes the zoned decimal field is in the form specified by the current job. The following rules apply:

For converting a zoned decimal field to a date field: The system assumes the zoned decimal field data is in the same date format (no separators) as specified in the current job under which the copy command is running. This might be *MDY, *DMY, *YMD, or *JUL. The length of the zoned decimal field must be 5,0 (if the current job format is *JUL) or 6,0 (if the current job format is *MDY, *DMY, or *YMD). The system attempts to convert or copy it to the date to-field.

For converting a zoned decimal field to a time field: The system assumes the zoned decimal field data is in the *HMS format (no separators). The length of the zoned decimal field must be 6,0. The system attempts to convert or copy it to the time to-field.

For converting a zoned decimal field to a timestamp field: The system assumes the zoned decimal field data is in the YYYYMMDDHHMMSS form (no separators). The length of the zoned decimal field must be 14,0. The system attempts to convert or copy it to the timestamp to-field.

When converting a date, time, or timestamp field to a zoned decimal field: The FMTOPT(*MAP) is specified and the corresponding from-field and to-field names match, the system uses the specified format for the current job to determine what format the zoned decimal data should be in. The following rules apply:

For converting a date field to a zoned decimal field: The system assumes the zoned decimal field data is to be in the same date format (no separators) as specified in the current job under which the copy command is running. This might be *MDY, *DMY, *YMD, or *JUL. The length of the zoned decimal field must be 5,0 (if the current job format is *JUL) or 6,0 (if the current job format is *MDY, *DMY, or *YMD). The system attempts to convert or copy the date field to it.

For converting a time field to a zoned decimal field: The system assumes the zoned decimal field data is to be in the *HMS format (no separators). The length of the zoned decimal field must be 6,0. The system attempts to convert or copy the time field to it.

For converting a timestamp field to a zoned decimal field: The system assumes the zoned decimal field data is to be in the YYYYMMDDHHMMSS form (no separators). The length of the zoned decimal field must be 14,0. The system attempts to convert or copy the timestamp field to it.

Any conversion not successful because of a data value, data format, or data-length error causes an information message to be sent. The to-file field is set with its default value.

Parent topic:
Specifying data for different field types and attributes

Related information

DDS concepts