Loading a Date column on SQL*Loader
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Loading a Date column on SQL*Loader

Hybrid View

  1. #1
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Wink



    I have a date column and the data I am loading is something like:
    Wed Mar 21 12:45:14 EST 2001

    so, how do I load the date with the "EST" and everything.
    I know if I were to convert SYSDATE I would do something like:
    select to_char(sysdate, 'Dy Month DD HH24:MI:SS "EST" YYYY') from dual;

    My MAIN problem is the "EST" thing.
    But, in SQL*Loader, I am confused how to tell it to see the date. The documentation regarding this is very confusing.

    I have been changing my controlfile around.
    My controlfile right now is:
    OPTIONS ()
    LOAD
    TRUNCATE
    INTO table app_ticker_price
    FIELDS terminated by ","
    (
    Ticker enclosed by '"',
    EquityName enclosed by '"',
    Price,
    Changedate date "DD-MON-YYYY"
    )


    thanks for the help.
    - Magnus

  2. #2
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Unhappy ALSO



    Well, maybe if I put it another way:

    if I do this on my Solaris box:
    shamrock% date
    Wed Mar 21 15:07:54 EST 2001


    How does ORACLE covert this to SYSDATE formula?
    How does it ignore/erase the "EST"???


    thanks,
    Magnus

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    jgmagnus the date in solaris has nothing to do the sysdate in oracle if I am correct?
    BTW in Metalink someone asked how to convert sysdate to GMT

    the suggestion were
    use NEW_TIME function

    Eg :
    select to_char( NEW_TIME( to_date ('1206212000', 'MMDDHH24YYYY'), 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') from dual;

    But from 8.1.5 upwards only

    You can also have a look at this paper
    [url]http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=2069892.6[/url]

  4. #4
    Join Date
    May 2000
    Location
    Portsmouth, NH, USA
    Posts
    378

    Talking


    Thanks for replying Pando.

    As I understood it, ORACLE gets it's date/time from the server it is installed on (Solaris in this case). So, if I change the server time-stamp by one hour, the Oracle sysdate time will be off by that same hour. That has been my understanding/experience.

    Now, HOW Oracle converts the "date" parameter on the server-side into sysdate, I have no idea.

    I'll check the link you gave me.
    thanks.

    - Magnus

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