i hate dates & sqlldr
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: i hate dates & sqlldr

  1. #1
    Join Date
    Oct 2002
    Posts
    182

    Angry 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

  2. #2
    Join Date
    Oct 2002
    Posts
    182

    Thumbs up 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
  •  


Click Here to Expand Forum to Full Width