Re: SQL*LOADER: converting ulong date-time to oracle format

A copy of this was sent to Furkan Khan <khanf_at_home.com> (if that email address didn't require changing) On Mon, 10 May 1999 17:33:33 GMT, you wrote:

>Folks, I want to use SQL*LOADER to load a bunch of data
>from the CSV file. One of the columns is the date/time stamp
>in "ulong", i.e., an integer number.
>
>Is there a way to convert a "ulong" date/time to oracle date ??
>
>Any help appreciated.
>
>Thanks in advance.
>
>Furkan

Don't know what you mean by ulong except that its probably an unsigned long. If that number happens to be the of seconds since 1/1/1970 GMT, then the following sqlfunction can be applied:

new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :x, 'GMT', 'EDT' )

on input to convert the ulong to a time.

For example, the control file:

LOAD DATA
INFILE *
INTO TABLE T
REPLACE
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (x char "new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :x, 'GMT', 'EDT ' )"
)
BEGINDATA
123456789
892430675

results in:

SQL> select to_char(x,'dd-mon-yyyy hh24:mi:ss' ) from t;

TO_CHAR(X,'DD-MON-YY

fix up your time zone OR add n/24 to the to_date('01011970','ddmmyyyy') + 1/24/60/60 * :x where N is your offset from GMT.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

-- 
Opinions are mine and do not necessarily reflect those of Oracle Corporation