Solved

convert string to HH24:MM:SS in oracle

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

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 77

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 77

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to connect SQL Server from my Oracle database? 11 131
Queries 15 46
How to Gracefuly recover in Racle stored procedure 1 33
create a nested synonym 4 28
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
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.

861 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