CODEWITHSUNDEEP
oracle-database
A12
A12

Reputation: 17

Oracle convert hhmmss to hh:mm:ss

I have some timestamp data in an old Oracle database that needs converting into HH:MM:SS. After trying to use to_char function, the value I give is not readable (E.g. 105001, to_char('105001','HH24:MI:SS)), this SQL will break. I can convert sysdate into the incorrect format but I can't reverse the procedure.

For example: 

select to_char(sysdate, 'HHmiss')from table

returns '105001'

I need something that will convert the hhmmss format into HH:MM:SS so when I produce a select statement it is in a readable format.

Upvotes: 0

Views: 11360

Answers (3)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

Assuming that your values are a NUMBER in the database which is six-digits long and represents an HHMMSS value you can format it as you want by using SUBSTR:

SELECT SUBSTR(TO_CHAR(SOME_TIMESTAMP, 'FM000000'), 1, 2) || ':' ||
       SUBSTR(TO_CHAR(SOME_TIMESTAMP, 'FM000000'), 3, 2) || ':' ||
       SUBSTR(TO_CHAR(SOME_TIMESTAMP, 'FM000000'), 5, 2)
  FROM cteNumbers

db<>fiddle here

Upvotes: 3

Pedram Ezzati
Pedram Ezzati

Reputation: 325

You can first select from dual table which is virtual table

There are 2 different way to have time

24 hours : like 5 and 15

select to_char(sysdate, 'HH24:MI:SS')from dual

Result

14:25:56

12 hours : like 2 AM and 2 PM

select to_char(sysdate, 'HH:MI:SS AM')from dual

Result

02:22:35 PM

Upvotes: 2

MT0
MT0

Reputation: 168026

I have some timestamp data in an old Oracle database that needs converting into HH:MM:SS

Just use HH24 to get a 24-hour clock and add the : separators to your format model and then apply that format directly to your TIMESTAMP column using the TO_CHAR function:

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( your_timestamp_column TIMESTAMP );

INSERT INTO table_name ( your_timestamp_column )
  VALUES ( TIMESTAMP '2018-09-24 12:34:56' );

Query 1:

SELECT TO_CHAR( your_timestamp_column, 'HH24:MI:SS') FROM table_name

Results:

| TO_CHAR(YOUR_TIMESTAMP_COLUMN,'HH24:MI:SS') |
|---------------------------------------------|
|                                    12:34:56 |

You do not need to output it as a HHMMSS string and then try to reformat it to add separators as that is needlessly complicated.

Upvotes: 1

Related Questions