'Convert Varchar2 24h time to 12h format SQL

I have a Varchar2 column in a table that contains a time like '13:24:02'. I want to convert the value into 12h format with am/pm eg. 01:24:02 PM

Is there any way to do it? Tried to_char and to_date but isn't giving me the result that I wanted.

sqloracle


Solution 1:[1]

Convert it to a date and then back to a string in the correct format:

SELECT TO_CHAR(TO_DATE(your_column, 'HH24:MI:SS'), 'HH12:MI:SS AM')
         AS formatted_time
FROM   your_table

Which, for the sample data:

CREATE TABLE your_table (your_column) AS
SELECT '13:24:02' FROM DUAL;

Outputs:

FORMATTED_TIME
01:24:02 PM

db<>fiddle here

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 MT0

Related Questions