OraFAQ Forum: Server Utilities » SQLLDR Import Time string as seconds

Home » RDBMS Server » Server Utilities » SQLLDR Import Time string as seconds (ORacle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR Import Time string as seconds [message #382659]

Fri, 23 January 2009 08:43

stowelle
Messages: 6
Registered: January 2009
Location: Bristol

Junior Member

Hi,

I'm currently trying to import a time field using sqlldr. My problem is that in the CSV file my text is in the format "24HH:MM:SS" but I want it to be in the table as a numerical representation of the number of seconds ie if the data value is "10:00:00" then I want it to be imported into the table as the number 36000 with the data type number.

Any help would be much appreciated.

Thanks,

Emma

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382660 is a reply to message #382659]

Fri, 23 January 2009 09:00

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>I want it to be imported into the table as the number 36000 with the data type number.
This is a bad idea/plan.
Dates (& times)should be stored as DATE datatype.
You are confusing how the data is store with how the data is presented.
'10:00:00' is a string & so is '36000'

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you & please be consistent & correct in your postings.

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382685 is a reply to message #382659]

Fri, 23 January 2009 10:15

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Create a function that converts you date string into your number and call it in your control file.

Regards
Michel

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382697 is a reply to message #382685]

Fri, 23 January 2009 10:44

stowelle
Messages: 6
Registered: January 2009
Location: Bristol

Junior Member

Sorry if I sound stupid but I'm not sure what function to write, I've tried using
LOGIN_TIME "TO_NUMBER(TO_DATE(:LOGIN_TIME,'HH24:Mi:SS'))"
but to no avail I get the message invalid number.

The reason I want to do it is because the data is in fact a duration and should not have been stored in this format originally it should have been stored as a number of seconds.

Many thanks for all your help.

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382698 is a reply to message #382659]

Fri, 23 January 2009 10:45

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Since when is ":" part of a valid number?

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382699 is a reply to message #382697]

Fri, 23 January 2009 10:57

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> select to_number(to_char(to_date('10:00:00','HH24:MI:SS'),'SSSSS')) from dual;
TO_NUMBER(TO_CHAR(TO_DATE('10:00:00','HH24:MI:SS'),'SSSSS'))
------------------------------------------------------------
                                                       36000

1 row selected.

Regards
Michel

Report message to a moderator

Re: SQLLDR Import Time string as seconds [message #382896 is a reply to message #382699]

Mon, 26 January 2009 03:02

stowelle
Messages: 6
Registered: January 2009
Location: Bristol

Junior Member

Many Thanks for your help.

Report message to a moderator

Previous Topic:	expdb can corrupt data
Next Topic:	flashback limit

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Apr 28 01:47:11 CDT 2024