Time format from 11:00:00 AM to 11:00:00 in Oracle

Question

In the Table REC_ALL_TRADES I have the column as CALCTM which have the value like 11:00:00 AM and other also with out am. I want to remove the AM from the value. Can you please let me know how to do this?

Answer 1

If the data type of the CALCTM column is a DATE or TIMESTAMP then the data does not have a format. Oracle stores DATE data types as 7-bytes (and similar for TIMESTAMP data types) and it is not until it is passed to a client program (i.e. SQL/Plus, SQL Developer, Toad, Java, Python, etc) and that client program formats it according to whatever rules it has that the date gets a format.

You can give DATE and TIMESTAMP columns a format using TO_CHAR( datevalue, format_model, [nls_settings] ) like this:

SELECT TO_CHAR( CALCTM, 'HH24:MI:SS' )
FROM   REC_ALL_TRADES

If it is stored as a string then convert it to a DATE and then back to a string:

SELECT TO_CHAR(
         TO_DATE( CALCTM, 'HH12:MI:SS AM' ),
         'HH24:MI:SS'
       )
FROM   REC_ALL_TRADES

If you have mixed valid/invalid formatted strings then use a CASE statement to differentiate:

SELECT CASE
       WHEN SUBSTR( TRIM( UPPER( CALCTM ) ), -1 ) IN ( 'AM', 'PM' )
       THEN TO_CHAR(
              TO_DATE( CALCTM, 'HH12:MI:SS AM' ),
              'HH24:MI:SS'
            )
       ELSE CALCTM
       END
FROM   REC_ALL_TRADES