The DATEFORMAT command is used to set the format of date/time fields before updating Relational target tables.
For Oracle and SQL Server target datastores, the string value specified in the DATEFORMAT command is passed to the Oracle or SQL Server system to convert the incoming date/time value to one that Oracle or SQL Server supports. The supported date/time formats can be found in the Oracle SQL and SQL Server Reference documentation.
For DB2 target datastores, the source date value is converted to the DB2 date format based on the DATEFORMAT string value specified. The supported date/time formats can be found in the DB2 SQD Reference.
The DATEFORMAT commands can be specified on global level (i.e. no field specification) and/or a field level. Global specification applies to all relational target database tables in the script. If a global specification is used, it must precede any field level specifications, since field level DATEFORMAT commands override a previously specified global level DATEFORMAT command.
DATEFORMAT [<field_name>] <format_string> | 'ISO' | 'ISOIBM' ;| Keyword | Description |
|---|---|
| <field_name> |
An optional target field name that if specified, with take on the date format. If this parameter is specified, the DATEFORMAT is considered a field level specification; otherwise it is considered a global specification. |
| <format_string> |
The required date/time format of the target field before it is updated into Oracle, SQL Server or DB2. For Oracle and SQL Server, this string is any value for date format. The supported formats (i.e. YYYY-MM-DD) are listed in the Oracle SQL and SQL Server Reference manuals. For DB2, the format string must be one of ISO, USA, EUR, or JIS. These date formats are what DB2 allows, and based on the string specified, the resulting target field is converted accordingly. |
| ISO |
The target will be formatted according to the following interpretation of the ISO 8601 standard 'YYYY-MM-DDTHH.MM.SS.FF6' |
| ISOIBM |
Intended for Db2/z and Db2/LUW which substitutes a hyphen for the T separator in the ISO timestamp, 'YYYY-MM-DD-HH.MM.SS.FF6' |
Example 1
DATEFORMAT 'YYYY-MM-DD HH24.MI';
DATEFORMAT ORA_TBL1.ORA_COL1 'YYYY-MM-DD HH24.MI.SS';Example 2
DATEFORMAT 'ISOIBM';
DATEFORMAT DB2_TBL1.DB2_COL1 'USA';Example 3
DATEFORMAT <description>.<column_name> 'YYYY-MM-DD-HH24.MI.SS.FF6';