When using the TO_CHAR() function to format a datetime value in Oracle Database, you can add the meridiem indicator (AM/PM) by simply adding either AM or PM to your format model.

Oracle then displays the appropriate meridiem indicator, depending on whether the time value is AM or PM.

You can provide it in any uppercase or lowercase, and with or without dots (e.g. AM, A.M. am, a.m, etc). Oracle will then display the meridiem indicator as specified.

Example

Here’s an example to demonstrate:

SELECT TO_CHAR(timestamp '2030-12-30 08:30:45', 'HH:MI:SS AM') 
FROM DUAL;

Result:

08:30:45 AM

In this case, the time is ante meridiem, and so the result has AM appended.

AM vs PM

The good thing about it is that Oracle works out whether it’s AM or PM, and displays the applicable meridiem indicator.

So here’s what happens when I change the time to a PM value:

SELECT TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS AM') 
FROM DUAL;

Result:

06:30:45 PM

The meridiem indicator is now PM, even though my format string is AM. Oracle was smart enough to know that the time is post meridiem.

Uppercase vs Lowercase

Changing the case of the format element changes the case of the result:

SELECT 
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS AM') AS "Uppercase",
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS am') AS "Lowercase",
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS Am') AS "Mixed"
FROM DUAL;

Result:

     Uppercase      Lowercase          Mixed 
______________ ______________ ______________ 
06:30:45 PM    06:30:45 pm    06:30:45 Pm    

Dots vs No Dots

You can include dots if you prefer:

SELECT 
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS A.M.') AS "Uppercase",
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS a.m.') AS "Lowercase",
    TO_CHAR(timestamp '2030-12-30 18:30:45', 'HH:MI:SS A.m.') AS "Mixed"
FROM DUAL;

Result:

       Uppercase        Lowercase            Mixed 
________________ ________________ ________________ 
06:30:45 P.M.    06:30:45 p.m.    06:30:45 P.M.   

Although, notice that this affected the mixed case meridiem indicator.

Today’s Date & Time

Here, I pass SYSDATE to get the current date/time:

SELECT 
    TO_CHAR(SYSDATE, 'HH:MI:SS A.M.')
FROM DUAL;

Result:

07:55:57 P.M.