DATE field contains character data that should be converted to an Oracle date using the specified date mask. The syntax for the
DATE field is:
LOAD DATA INTO TABLE dates (col_a POSITION (1:15) DATE "DD-Mon-YYYY") BEGINDATA 1-Jan-2012 1-Apr-2012 28-Feb-2012
Whitespace is ignored and dates are parsed from left to right unless delimiters are present. (A
DATE field that consists entirely of whitespace is loaded as a
The length specification is optional, unless a varying-length date mask is specified. The length is in bytes unless character-length semantics are used for the data file. In that case, the length is in characters. See "Character-Length Semantics".
In the preceding example, the date mask,
"DD-Mon-YYYY" contains 11 bytes, with byte-length semantics. Therefore, SQL*Loader expects a maximum of 11 bytes in the field, so the specification works properly. But, suppose a specification such as the following is given:
DATE "Month dd, YYYY"
In this case, the date mask contains 14 bytes. If a value with a length longer than 14 bytes is specified, such as
"September 30, 2012", then a length must be specified.
Similarly, a length is required for any Julian dates (date mask "J"). A field length is required any time the length of the date string could exceed the length of the mask (that is, the count of bytes in the mask).
If an explicit length is not specified, then it can be derived from the
POSITION clause. It is a good idea to specify the length whenever you use a mask, unless you are absolutely sure that the length of the data is less than, or equal to, the length of the mask.
An explicit length specification, if present, overrides the length in the
POSITION clause. Either of these overrides the length derived from the mask. The mask may be any valid Oracle date mask. If you omit the mask, then the default Oracle date mask of "dd-mon-yy" is used.
The length must be enclosed in parentheses and the mask in quotation marks.
A field of data type
DATE may also be specified with delimiters. For more information, see "Specifying Delimiters".