-
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'
)
- Cookies
-
got it
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)",
- Cookies
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|