Loading a Date column on SQL*Loader

[jgmagnus]

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

[jgmagnus]

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

[pando]

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]

[jgmagnus]

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