i hate dates & sqlldr
Incoming file has date field only in HH24:MI:SS
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):
( SKIP = 5,
ERRORS = 1000,
ROWS = 128,
DIRECT = FALSE,
PARALLEL = FALSE
INTO TABLE T_PRICING
FIELDS TERMINATED BY ',' optionally enclosed by '"' TRAILING NULLCOLS
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_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:
MKT_PRICE_DT date 'DDMMYY HH24:MI' "to_char(sysdate, 'DDMMYY')||' '||substr(:MKT_PRICE_DT,1,5)",
Click Here to Expand Forum to Full Width