Solved

convert string to HH24:MM:SS in oracle

Posted on 2013-05-14
11
516 Views
Last Modified: 2013-06-05
how to convert  141059  to HH24:MM:SS in oracle
0
Comment
Question by:aboha
  • 3
  • 3
11 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 334 total points
ID: 39166235
Are you just wanting the colons added?

select regexp_replace('141059','([0-9]{2})([0-9]{2})([0-9]{2})','\1:\2:\3') from dual;


If not that, what exactly are you wanting to do?
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 334 total points
ID: 39166240
It's overkill but you could turn it into a date then back into a string:
select to_char(to_date('141059','HH24MISS'),'HH24:MI:SS') from dual;
0
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 166 total points
ID: 39167163
if converting to date then you might need to validate the string i.e. 141066 would fail

mind you just inserting colons would not fail but would look odd (14:10:66)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 32

Expert Comment

by:awking00
ID: 39168319
How is this conversion going to be used? Just for display, or input to a procedure or insert statement, or to apply some kind of date math, or what?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39205591
aboha,

Please explain why you accepted the post you did as the solution.  That post was a question and really did nothing to answer the question asked.

If it was just to get around the new question lock, this is inappropriate.

If you do not post back in the next day or two, I'll have this addressed by the Moderators.
0
 
LVL 32

Expert Comment

by:awking00
ID: 39222942
500 to 0 :-( No problem
0
 
LVL 32

Expert Comment

by:awking00
ID: 39223646
Netminder,
I'm not at all displeased with not getting any credit for my comment asking questions. I just thought it was funny to first be credited with all of the points (admittedly undeservingly so) then none. I just wish the asker had answered the questions so that we could help him if the result needed to be something other than a just string of characters.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question