Solved

convert string to HH24:MM:SS in oracle

Posted on 2013-05-14
11
514 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now