topic Re: date/time with millisecond datatype in Operating System - HP-UX

date/time with millisecond datatype

Edgar_8 — Mon, 11 Oct 2004 08:34:11 GMT

Hi,

I have a table which is being loaded with data via sqlldr, there are approx. 9 date/time columns on the
table. The situation we facing is that the raw CSV data files to be loaded have their date/time columns
down to milliseconds (ie. yyyymmddhh24miss.fff where fff is the millisecond value.)

Has anyone been able to load a date/time or timestamp column datatype with date that has milliseconds? We
have tried using the to_timestamp conversion function with the sqlldr control file with no success.
Oracle 9i HP-UX 11i

Thanks in advance!

Re: date/time with millisecond datatype

Hein van den Heuvel — Mon, 11 Oct 2004 09:28:13 GMT

This question would appear to be 100% oracle, and 0% platform dependend.
Wouldn't it be more effective ask an Oracle forum / newsgroup / support ?

Some (non)starters:

http://expertanswercenter.techtarget.com/eac/knowledgebaseAnswer/0,295199,sid63_gci978411,00.html

http://www.dbforums.com/archive/index.php/t-323052.html

http://www.experts-exchange.com/Databases/Oracle/Q_10087218.html

Regards,
Hein.

Re: date/time with millisecond datatype

Jeff_Traigle — Mon, 11 Oct 2004 09:38:27 GMT

Well, he did at least put the question in the database forum.

Did a quick Google search for "DATE Oracle". The following article confirmed my rememberance of the DATE data type, but goes on to explain how the TIMESTAMP data type can be used to store fractional seconds as you are trying to do.

http://www.databasejournal.com/features/oracle/article.php/2234501

Re: date/time with millisecond datatype

Yogeeraj_1 — Mon, 11 Oct 2004 23:53:01 GMT

hi,

this should work! try this:

You can call SQL functions simply by including them in the control
file. For example:

LOAD DATA
INTO TABLE DEPT
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO,
DNAME,
LOC,
TIMESTAMP "function_call"
)

You may also wish to try the external tables facility.

unfortunately, i do not have an Oracle 9i Database to test.

hope this helps!

regards
Yogeeraj

Re: date/time with millisecond datatype

Edgar_8 — Tue, 12 Oct 2004 01:13:33 GMT

Hi Yogee,

Thanks for the feedback. Please could you shed some light on the below:
- what datatype(date or timestamp) should the date/time columns be create in the table sql?
- explain in more detail how the control file syntax should be for the date or timestamp datatype columns

Thanks in advance!

Re: date/time with millisecond datatype

Bill Thorsteinson — Tue, 12 Oct 2004 09:03:03 GMT

If you need milliseconds you will need
to define the columns as TIMESTAMP.

Assuming you have found 'fff' as the format
for milliseconds, the colulmn defs in the
control files should look like:

column_xx TIMESTAMP 'yyyymmddhh24miss.ff',

You may add the standard NULLIF clause if
required.

Re: date/time with millisecond datatype

Yogeeraj_1 — Wed, 13 Oct 2004 02:43:24 GMT

hi,

sorry for this late reply.

See what i managed to get tested:
=======================================
yd@MYDB.MU> create table t ( x timestamp );

Table created.

yd@MYDB.MU> !cat t.ctl
LOAD DATA
INFILE *
INTO TABLE T
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(X timestamp "yyyymmddhh24miss.ff" )
BEGINDATA
20041012081522.123

yd@MYDB.MU> !sqlldr / t

SQL*Loader: Release 9.2.0.5.0 - Production on Wed Oct 13 11:18:45 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Commit point reached - logical record count 1

yd@MYDB.MU> select * from t;

X
---------------------------------------------------------------------------
12-OCT-04 08.15.22.123000 AM

===========================================
Hope this helps!
regards
Yogeeraj

Re: date/time with millisecond datatype

Yogeeraj_1 — Wed, 13 Oct 2004 02:45:23 GMT

hi again,

hope you can adapt it to your situation.

In case of any other difficulties, do let us know.

best regards
Yogeeraj