When a SQL string is used with a date mask, the date mask is evaluated after the SQL string. Consider a field specified as follows:
field1 DATE "dd-mon-yy" "RTRIM(:field1)"
SQL*Loader internally generates and inserts the following:
Note that when using the
DATE field data type with a SQL string, a date mask is required. This is because SQL*Loader assumes that the first quoted string it finds after the
DATE parameter is a date mask. For instance, the following field specification would result in an error (ORA-01821: date format not recognized):
field1 DATE "RTRIM(TO_DATE(:field1, 'dd-mon-yyyy'))"
In this case, a simple workaround is to use the
CHAR data type.