Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

convert string to HH24:MM:SS in oracle

Posted on 2013-05-14
11
Medium Priority
?
552 Views
Last Modified: 2013-06-05
how to convert  141059  to HH24:MM:SS in oracle
0
Comment
Question by:aboha
  • 3
  • 3
7 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1336 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 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1336 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 49

Assisted Solution

by:PortletPaul
PortletPaul earned 664 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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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 78

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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

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…
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 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…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

581 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