Supported external DATE TIME and TIMESTAMP formats
The tables in this topic list input date, time, and timestamp formats, other than internal Db2 formats, that LOADPLUS supports.
The format value listed in these tables is the value that you use in place of the format in the DATE-format EXTERNAL, TIME-format EXTERNAL, and TIMESTAMP-format EXTERNAL data type specification.
External date formats
The following table describes the date formats:
Format value Char/Int/Dec | Nondelimited format | Format value Char | Delimited format 1 | ||
|---|---|---|---|---|---|
1 | I1 | P1 | MMDDYY | D1 | MM/DD/YY |
2 | I2 | P2 | YYMMDD | D2 | YY/MM/DD |
3 | I3 | P3 | YYDDD | D3 | YY/DDD |
4 | I4 | P4 | YYWWD | D4 | YY/WW/D |
5 | I5 | P5 | DDMMYY | D5 | DD/MM/YY |
6 | I6 | P6 | DDDDD |
|
|
1E | I1E | P1E | MMDDYYYY | D1E | MM/DD/YYYY |
2E | I2E | P2E | YYYYMMDD | D2E | YYYY/MM/DD |
3E | I3E | P3E | YYYYDDD | D3E | YYYY/DDD |
4E | I4E | P4E | YYYYWWD | D4E | YYYY/WW/D |
5E | I5E | P5E | DDMMYYYY | D5E | DD/MM/YYYY |
1 '/' can be any nonnumeric character. However, for FORMAT CSV, do not use delimiting characters that you specify with TERMINATEDBY or ENCLOSEDBY.
External time formats
The following table describes the time formats:
Format value Char/Int/Dec | Nondelimited format | Format value Char | Delimited format 1 | ||
|---|---|---|---|---|---|
1 | I1 | P1 | HHMMSS | D1 | HH.MM.SS |
2 | I2 | P2 | HHMM | D2 | HH.MM |
Not applicable | Not applicable | Not applicable | Not applicable | D3 | HH.MM xM 2 |
1 '.' can be any nonnumeric character. However, for FORMAT CSV, do not use delimiting characters that you specify with TERMINATEDBY or ENCLOSEDBY.
2 xM is either AM or PM.
External timestamp formats
Any of the following timestamp formats except format 3 can include the time zone offset in the format ±HH:MM.
The following table describes the timestamp formats:
Format value | Nondelimited format | Format value | Delimited format 1 |
|---|---|---|---|
1 | YYMMDDHHMMSS | D1 | YY-MM-DD-HH.MM.SS |
2 | YYMMDDHHMMSSNNNNNN 2 | D2 | YY-MM-DD-HH.MM.SS.NNNNNN 2 |
3 | Store clock 8-byte binary | Not applicable | Not applicable |
1E | YYYYMMDDHHMMSS | D1E | YYYY-MM-DD-HH.MM.SS |
2E | YYYYMMDDHHMMSSNNNNNN 2 | D2 | YYYY-MM-DD-HH.MM.SS.NNNNNN 2 |
1 '-' and '.' can be any nonnumeric character except delimiting characters that you specify with TERMINATEDBY or ENCLOSEDBY
2 NNNNNN can be any number of microseconds from 1 through 12. For FORMAT CSV, NNNNNN must be less than or equal to the precision defined in the column that you are loading.
Related topic