i hate dates & sqlldr

frickin dates!
ugh!

Situation:
Incoming file has date field only in HH24:MI:SS
Example: 14:00:02

WHat I need:
Need to load this time as the current day with those hours and minutes, but 00 seconds (round or set to 00).
Example: 07/03/2003 14:00:00

I can't seem to get this done and I have tried everything.
If I can avoid it, I would like to NOT have to load this into a temporary table and just load it directly into the real DB table.

I tried just laoding just the time (which loaded), but it doesn't load the current day with it.

Current ctl file looks something like this (obviously the dat-thing does not work):

OPTIONS
( SKIP = 5,
ERRORS = 1000,
ROWS = 128,
DIRECT = FALSE,
PARALLEL = FALSE
)

LOAD DATA
INFILE 'rolling-5min.csv'
BADFILE 'load_pricing.bad'
DISCARDFILE 'load_pricing.dis'
DISCARDMAX 10000000

APPEND
INTO TABLE T_PRICING
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
(
dummy1 FILLER,
MKT_LOCTN_ID CHAR,
MKT_PRICE_DT date 'DDMMYY HH24:MI:SS' terminated by "," "to_date(to_char(sysdate, 'DDMMYY')||' '||:MKT_PRICE_DT,'DDMMYY HH24:MI:SS')",
MKT_PRCNG_TYPE_CD constant '5M',
ENERGY_NBR CHAR,
CNGSTN_NBR CHAR,
MRGNL_LS_NBR CHAR,
LMP_NBR CHAR,
ENERGY_MKT_CD constant 'NEISO'
)

man this took forever.
Helped to throw it into a character column first to see how I was manipulating it.

If interested, here was the line that did it:

PHP Code:

---

MKT_PRICE_DT date 'DDMMYY HH24:MI' "to_char(sysdate, 'DDMMYY')||' '||substr(:MKT_PRICE_DT,1,5)", 


---