Dates & Timestamps

Date and timestamp columns are the most error-prone part of a sqlldr load. The format mask must exactly match the source string — a single character mismatch silently rejects every date row into the bad file.

Always specify an explicit format mask

-- WRONG: relies on NLS_DATE_FORMAT session setting — fragile
hire_date DATE

-- RIGHT: explicit mask — load always works regardless of NLS
hire_date DATE "YYYY-MM-DD"

Common date format masks

Source string	Format mask
2024-01-15	DATE "YYYY-MM-DD"
15/01/2024	DATE "DD/MM/YYYY"
01/15/2024	DATE "MM/DD/YYYY"
20240115	DATE "YYYYMMDD"
15-JAN-24	DATE "DD-MON-YY"
15-JAN-2024	DATE "DD-MON-YYYY"
January 15, 2024	DATE "MONTH DD, YYYY"
2024015 (Julian)	DATE "YYYYDDD"

Format mask tokens are NLS-independent within sqlldr. YYYY, MM, DD always mean four-digit year, two-digit month, two-digit day.

Date with time

-- "2024-01-15 14:30:00"
event_time  DATE "YYYY-MM-DD HH24:MI:SS"

-- "2024-01-15T14:30:00" (ISO 8601)
event_time  DATE "YYYY-MM-DD\"T\"HH24:MI:SS"

-- "15-JAN-24 02:30:00 PM"
event_time  DATE "DD-MON-YY HH:MI:SS AM"

TIMESTAMP

-- "2024-01-15 14:30:00.000000"
created_at  TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6"

-- "2024-01-15 14:30:00.123"
created_at  TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF3"

-- "2024-01-15 14:30:00+05:30" (with timezone)
created_at  TIMESTAMP WITH TIME ZONE "YYYY-MM-DD HH24:MI:SS TZH:TZM"

FF alone defaults to 6 fractional digits. FF3, FF6, FF9 specify 3, 6, or 9 digits explicitly.

TIMESTAMP WITH LOCAL TIME ZONE

created_at  TIMESTAMP WITH LOCAL TIME ZONE "YYYY-MM-DD HH24:MI:SS.FF6"

Oracle converts the loaded timestamp to the database's time zone and stores it normalised. The source timestamp is assumed to be in the session time zone.

Using TO_DATE / TO_TIMESTAMP in a SQL expression

When the mask is complex or the source has non-standard formats, delegate to a SQL function:

(
  order_id   INTEGER EXTERNAL,
  order_date CHAR(20) "TO_DATE(:order_date, 'YYYY-MM-DD')",
  ship_ts    CHAR(30) "TO_TIMESTAMP(:ship_ts, 'YYYY-MM-DD HH24:MI:SS.FF')"
)

The field is first read as CHAR, then the SQL expression transforms it. This lets you use the full power of Oracle's date/time functions.

-- Unix epoch → DATE
epoch_secs  CHAR(12) "TO_DATE('1970-01-01','YYYY-MM-DD') + :epoch_secs/86400"

-- Relative date arithmetic
load_date   CHAR(8) "TO_DATE(:load_date,'YYYYMMDD') - 1"  -- yesterday

Handling optional / nullable dates

(
  order_id     INTEGER EXTERNAL,
  ship_date    DATE "YYYY-MM-DD" NULLIF ship_date = '0000-00-00',
  cancel_date  DATE "YYYY-MM-DD" NULLIF cancel_date = BLANKS
)

NULLIF is evaluated before the format conversion — if the raw string matches the condition, the column is set to NULL without any format-mask attempt. This avoids errors from placeholder date strings like 0000-00-00.

Two-digit years and the RR format

-- "15-JAN-99" — what year?
hire_date  DATE "DD-MON-RR"    -- Oracle RR: 99 → 1999, 01 → 2001
hire_date  DATE "DD-MON-YY"    -- SQL YY: adds current century → 2099 if you're in 2000s

Always use RR (not YY) when loading legacy two-digit year data from the 20th century.

Full example — loading events with mixed timestamps

-- events_load.ctl
OPTIONS ( ERRORS=50, ROWS=10000 )
LOAD DATA
CHARACTERSET AL32UTF8
INFILE 'events.csv'
APPEND
INTO TABLE audit.events
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
  event_id     INTEGER EXTERNAL,
  user_id      INTEGER EXTERNAL,
  event_type   CHAR(30),
  event_time   TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6",
  server_time  DATE "YYYY-MM-DD HH24:MI:SS",
  client_tz    CHAR(6),
  resolved_at  DATE "YYYY-MM-DD HH24:MI:SS" NULLIF resolved_at = BLANKS,
  payload      CHAR(4000)
)

Best practices

• Never omit the format mask — session NLS_DATE_FORMAT varies between environments
• Use TIMESTAMP "YYYY-MM-DD HH24:MI:SS.FF6" rather than DATE for any source that includes fractional seconds
• Add NULLIF col = BLANKS for all nullable date columns to handle empty fields gracefully
• When the source system provides Unix epochs, use a SQL expression to convert — don't try to match epoch integers with a date mask
• Test date parsing with sqlldr ... ROWS=5 and inspect the log for "ORA-01830: date format picture ends before converting entire input string" — the most common date mask error