Home » SQL & PL/SQL » SQL & PL/SQL » Change Number to Time format
Change Number to Time format [message #349475] Mon, 22 September 2008 00:46 Go to next message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
the existing database has a time in a wierd format where the date was imported without the 0s to show HHMMSS format.Hence for a call made at 00:12:41 .. the data is stored as 1241

I was told to use lpad(string, total number of digits, 'char') which adds 0s to the left to make it a 6 digit number so as to have it in HHMMSS format where 1241 will become 001241

My question is .. how to convert a number of 6 digits that is in HHMISS format to get an output of HH:MI:SS format.

I tried to_char(TIMEOFCALL,'HH:MI:SS') which results in an error.

any suggestions?

[Updated on: Mon, 22 September 2008 00:47]

Report message to a moderator

Re: Change Number to Time format [message #349483 is a reply to message #349475] Mon, 22 September 2008 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the type of TIMEOFCALL?
What is your Oracle version with 4 decimals.

Also please read OraFAQ Forum Guide.

Regards
Michel
Re: Change Number to Time format [message #349519 is a reply to message #349475] Mon, 22 September 2008 02:28 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Did you mean something like this

Processing ...
select :inp as original,
	to_char(:inp,'000000') as string,
	to_date(to_char(:inp,'000000'),'HH24MISS') as time
from dual
Query finished, retrieving results...
               ORIGINAL                  STRING           TIME        
-------------------------------------- ---------- ------------------- 
1241                                    001241      1/09/2008 0:12:41 

1 row(s) retrieved


Bye Alessandro

[Updated on: Mon, 22 September 2008 02:44]

Report message to a moderator

Re: Change Number to Time format [message #349525 is a reply to message #349519] Mon, 22 September 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Alessandro, please at least let OP answer the questions before answering.
And remember, don't put solution only hint or clue as requested in OraFAQ Forum Guide, "Responding to Posts" section:
Quote:
When responding to questions, if it is obviously a student with a homework assignment or someone else just learning, especially in the homework and newbies forums, it is usual to provide hints or clues, perhaps links to relevant portions of the documentation, or a similar example, to point them in the right direction so that they will research and experiment on their own and learn, and not provide complete solutions to problems. In cases where someone has a real-life complex work problem, or at least it may seem complex to them, it may be best to provide a complete demo and explanation.


Regards
Michel
Re: Change Number to Time format [message #349549 is a reply to message #349525] Mon, 22 September 2008 03:25 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Ok. I'll think about it next time.
Re: Change Number to Time format [message #349550 is a reply to message #349525] Mon, 22 September 2008 03:28 Go to previous messageGo to next message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
TIMEOFCALL IS THE COLOUMN IN THE TABLE THAT HOLDS THE DATA

MY ORACLE VERSION IS 9.0.1.4.0

Also .. I am not looking to include the date in the ouput .. I just want 001241 to show as 00:12:41
Re: Change Number to Time format [message #349552 is a reply to message #349475] Mon, 22 September 2008 03:44 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

Then you could have used ,

1. LPAD
2. SUBSTR and


instead of using TO_CHAR Function .

TO_CHAR funcion , the way you used is basically for converting Date to Character .

Thumbs Up
Rajuvan.
Re: Change Number to Time format [message #349555 is a reply to message #349552] Mon, 22 September 2008 04:02 Go to previous messageGo to next message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
what is the syntax for the substr function?
Re: Change Number to Time format [message #349556 is a reply to message #349550] Mon, 22 September 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Notorious96 wrote on Mon, 22 September 2008 10:28
TIMEOFCALL IS THE COLOUMN IN THE TABLE THAT HOLDS THE DATA

MY ORACLE VERSION IS 9.0.1.4.0

Also .. I am not looking to include the date in the ouput .. I just want 001241 to show as 00:12:41

1/ Don't post in UPPER CASE
2/ That was not my question, my question was what TYPE (means datatype)
3/ Read TO_CHAR function and related format to only get time part.

Regards
Michel
Re: Change Number to Time format [message #349557 is a reply to message #349556] Mon, 22 September 2008 04:05 Go to previous messageGo to next message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
Sorry about posting in Uppercase .. I was typing statement in the uppercase and didnt' realize capslock was on ..

the datatype is Number ..
Re: Change Number to Time format [message #349560 is a reply to message #349475] Mon, 22 September 2008 04:07 Go to previous messageGo to next message
rajavu1
Messages: 1574
Registered: May 2005
Location: Bangalore , India
Senior Member

How can One be so lazy to search in the google for the syntax ?

Substr

Thumbs Up
Rajuvan
Re: Change Number to Time format [message #349561 is a reply to message #349557] Mon, 22 September 2008 04:08 Go to previous messageGo to next message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
I forgot to mention something .. the coloumn only contains the time .. in 1241 format .. it doesn't contain the date.

Meaning the data is present as follows

TIMEOFCALL
1241
4521
3224

etc. .. not in the 200809201241 format where date is also present.

Only the time is present

thanks for all the help in advance!
Re: Change Number to Time format [message #349562 is a reply to message #349552] Mon, 22 September 2008 04:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
here's a couple of ways fo doing it:
select regexp_replace('001241','([0-9]{2})([0-9]{2})([0-9]{2})','\1:\2:\3') from dual;

select replace(to_char(1241,'00,00,00'),',',':') from dual;
Re: Change Number to Time format [message #349577 is a reply to message #349562] Mon, 22 September 2008 04:49 Go to previous message
Notorious96
Messages: 7
Registered: September 2008
Junior Member
thanks a ton JRowbottom.. the second statement worked like a charm Smile
Previous Topic: Insert values into few columns
Next Topic: Dynamic view creation
Goto Forum:
  


Current Time: Thu Dec 08 02:22:15 CST 2016

Total time taken to generate the page: 0.09792 seconds