When we find hours stored in a 24 hour format in a varchar2 column, and we have to display a.m., p.m. along with the hour, it can be done in the following two ways.

 

 

   SELECT CASE

    WHEN HOUR <  '1200'  THEN HOUR||' A.M.'

         ELSE HOUR||' P.M.'

        END

   FROM HOURS

 

Or, it can be done the following way too...

 

SQL> SELECT TO_CHAR( TO_DATE(SUBSTR(HOUR, 1,2), 'HH24'),'HH A.M.') HOUR FROM HOURS;