Supported external DATE TIME and TIMESTAMP formats
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 |
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.
Comments
Log in or register to comment.