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

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 10 May 1999 18:27:55 GMT
Message-ID: <373b23bd.22579998@192.86.155.100>

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

---

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 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 Received on Mon May 10 1999 - 13:27:55 CDT