Home » RDBMS Server » Server Utilities » oracle table (2 Merged)
oracle table (2 Merged) [message #560274] Wed, 11 July 2012 11:55 Go to next message
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 [message #560278 is a reply to message #560274] Wed, 11 July 2012 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
>my question is how do i remove the date and just get the time?
DATE datatype contains both a DATE & TIME portion

consider using INTERVAL instead

http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements001.htm#sthref150


Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: oracle table (2 Merged) [message #560280 is a reply to message #560274] Wed, 11 July 2012 12:38 Go to previous messageGo to next message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
How are you loading the data? Why is the date portion what seems like some random date in the past?
Re: oracle table (2 Merged) [message #560288 is a reply to message #560280] Wed, 11 July 2012 14:33 Go to previous messageGo to next message
l.scott25
Messages: 5
Registered: July 2012
Junior Member
When I created the table (column), I declare it as a date because time won't work. I load the data using ssis package.
Re: oracle table (2 Merged) [message #560289 is a reply to message #560288] Wed, 11 July 2012 14:37 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
> I load the data using ssis package.
please post URL where this SSIS package is documented.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: oracle table (2 Merged) [message #560290 is a reply to message #560289] Wed, 11 July 2012 17:59 Go to previous messageGo to next message
l.scott25
Messages: 5
Registered: July 2012
Junior Member
Not to be ignorant but why do you need the SSIS package documentation? Do you want to know the process of loading?

Maybe this will help.
Source file : flat file ( time format ex:6:30:00 PM)
Source destination : Column dataype (date)

flat file loaded without any errors.

I think the problem is that datatype is not proper for that column.
Re: oracle table (2 Merged) [message #560291 is a reply to message #560290] Wed, 11 July 2012 18:17 Go to previous messageGo to next message
BlackSwan
Messages: 26733
Registered: January 2009
Location: SoCal
Senior Member
Thanks for your cooperation.

http://en.wikipedia.org/wiki/SQL_Server_Integration_Services

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: oracle table (2 Merged) [message #560303 is a reply to message #560291] Wed, 11 July 2012 23:27 Go to previous messageGo to next message
Littlefoot
Messages: 21595
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.
Re: oracle table (2 Merged) [message #560412 is a reply to message #560303] Thu, 12 July 2012 10:32 Go to previous message
l.scott25
Messages: 5
Registered: July 2012
Junior Member
You are right, it was actually Dec 30,1899.
Previous Topic: sql loader error records
Next Topic: problem to import table data
Goto Forum:
  


Current Time: Sun Sep 27 11:17:13 CDT 2020