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

From: Thomas Kyte <>
Date: Mon, 10 May 1999 18:27:55 GMT
Message-ID: <373b23bd.22579998_at_192.86.155.100>

A copy of this was sent to Furkan Khan <> (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.

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:

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (x char "new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 * :x, 'GMT', 'EDT ' )"

results in:

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


29-nov-1973 17:33:09
12-apr-1998 21:24:35

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 for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
Oracle Service Industries
Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Mon May 10 1999 - 20:27:55 CEST

Original text of this message