Home » RDBMS Server » Server Utilities » SQLLDR Import Time string as seconds (ORacle 10g)
SQLLDR Import Time string as seconds [message #382659] Fri, 23 January 2009 08:43 Go to next message
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
Re: SQLLDR Import Time string as seconds [message #382660 is a reply to message #382659] Fri, 23 January 2009 09:00 Go to previous messageGo to next message
BlackSwan
Messages: 25042
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.
Re: SQLLDR Import Time string as seconds [message #382685 is a reply to message #382659] Fri, 23 January 2009 10:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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
Re: SQLLDR Import Time string as seconds [message #382697 is a reply to message #382685] Fri, 23 January 2009 10:44 Go to previous messageGo to next message
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.
Re: SQLLDR Import Time string as seconds [message #382698 is a reply to message #382659] Fri, 23 January 2009 10:45 Go to previous messageGo to next message
BlackSwan
Messages: 25042
Registered: January 2009
Location: SoCal
Senior Member
Since when is ":" part of a valid number?
Re: SQLLDR Import Time string as seconds [message #382699 is a reply to message #382697] Fri, 23 January 2009 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
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
Re: SQLLDR Import Time string as seconds [message #382896 is a reply to message #382699] Mon, 26 January 2009 03:02 Go to previous message
stowelle
Messages: 6
Registered: January 2009
Location: Bristol
Junior Member
Many Thanks for your help.
Previous Topic: expdb can corrupt data
Next Topic: flashback limit
Goto Forum:
  


Current Time: Tue Dec 06 16:27:27 CST 2016

Total time taken to generate the page: 0.05118 seconds