- Community Home
- >
- Servers and Operating Systems
- >
- Operating Systems
- >
- Operating System - HP-UX
- >
- date/time with millisecond datatype
Categories
Company
Local Language
Forums
Discussions
Forums
- Data Protection and Retention
- Entry Storage Systems
- Legacy
- Midrange and Enterprise Storage
- Storage Networking
- HPE Nimble Storage
Discussions
Discussions
Discussions
Forums
Forums
Discussions
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
- BladeSystem Infrastructure and Application Solutions
- Appliance Servers
- Alpha Servers
- BackOffice Products
- Internet Products
- HPE 9000 and HPE e3000 Servers
- Networking
- Netservers
- Secure OS Software for Linux
- Server Management (Insight Manager 7)
- Windows Server 2003
- Operating System - Tru64 Unix
- ProLiant Deployment and Provisioning
- Linux-Based Community / Regional
- Microsoft System Center Integration
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Discussion Boards
Community
Resources
Forums
Blogs
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-11-2004 01:34 AM
тАО10-11-2004 01:34 AM
date/time with millisecond datatype
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-11-2004 02:28 AM
тАО10-11-2004 02:28 AM
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-11-2004 02:38 AM
тАО10-11-2004 02:38 AM
Re: date/time with millisecond datatype
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-11-2004 04:53 PM
тАО10-11-2004 04:53 PM
Re: date/time with millisecond datatype
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-11-2004 06:13 PM
тАО10-11-2004 06:13 PM
Re: date/time with millisecond datatype
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!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-12-2004 02:03 AM
тАО10-12-2004 02:03 AM
Re: date/time with millisecond datatype
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-12-2004 07:43 PM
тАО10-12-2004 07:43 PM
Re: date/time with millisecond datatype
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
тАО10-12-2004 07:45 PM
тАО10-12-2004 07:45 PM
Re: date/time with millisecond datatype
hope you can adapt it to your situation.
In case of any other difficulties, do let us know.
best regards
Yogeeraj