Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

date/time with millisecond datatype

Edgar_8
Regular Advisor

date/time with millisecond datatype

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!
7 REPLIES
Hein van den Heuvel
Honored Contributor

Re: date/time with millisecond datatype


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.
Jeff_Traigle
Honored Contributor

Re: date/time with millisecond datatype

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
--
Jeff Traigle
Yogeeraj_1
Honored Contributor

Re: date/time with millisecond datatype

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Edgar_8
Regular Advisor

Re: date/time with millisecond datatype

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!
Bill Thorsteinson
Honored Contributor

Re: date/time with millisecond datatype

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.
Yogeeraj_1
Honored Contributor

Re: date/time with millisecond datatype

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: date/time with millisecond datatype

hi again,

hope you can adapt it to your situation.

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

best regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)