| oracle table (2 Merged) [message #560274] |
Wed, 11 July 2012 11:55  |
 |
l.scott25
Messages: 5
Registered: July 2012
|
Junior Member |
|
|
hi you all, I'm new to oracle/pl/sql.
I have flat file that needs to be loaded in n oracle database, so I created a table. I don't know how to use timestamp in oracle so i used date instead for one of my column.
problem: the value in that column is in time format so when load the data this is what happened:
flat file (6:30:00)
oracle table: column_time ( 10/12/1899 6:30:00 PM)
my question is how do i remove the date and just get the time?
thanks in advance!
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: oracle table (2 Merged) [message #560303 is a reply to message #560291] |
Wed, 11 July 2012 23:27  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As far as I can tell (which you were told already): DATE column always contains both DATE and TIME, so no matter that you are inserting just TIME - Oracle will store DATE in there as well. Here's an example:SQL> create table test (col date);
Table created.
SQL> insert into test (col) values (to_date('06:11', 'hh24:mi'));
1 row created.
SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi:ss';
Session altered.
SQL> select col from test;
COL
-------------------
01.07.2012 06:11:00
SQL>
Date defaults to (database server's) "first of the current month". Now that's funny, as your date value seems to be "10/12/1899" (what are 10 and 12? Is "10" a typo and should be "30"?). Googling around, I stumbled over this OTN Forums discussion which says that - in MS Excel (which is *kind of* close to "SSIS"), day 1 is Jan 1st 1900. Due to misinterpreting 1900 to be a leap year (it is/was not), day 0 is Dec 30 1899.
That's why I asked whether "10" was a typo, hoping that it was actually 30th Dec.
You might jump into a wrong conclusion, thinking that you should store time value into a VARCHAR2 column. No problem with that ("storing"), but every other column manipulation will become rather painful and will - sooner or later - produce an error (because, why wouldn't you, some day, store "AB:78:3Y" as a "valid" time into that column?). So you'd rather stay with DATE.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
