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

Oracle then displays the appropriate indicator, depending on whether the date value is AD or BC.

You can provide it in any uppercase or lowercase, and with or without dots (e.g. AD, A.D. ad, a.d, etc). Oracle will then display the indicator as specified.

Example

Here’s an example to demonstrate:

SELECT 
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY AD')
FROM DUAL;

Result:

30-12-2030 AD

In this case, the date is AD, and so the result has AD appended.

AD vs BC

Oracle works out whether it’s AD or BC, and displays the applicable indicator.

Here’s what happens when I change the date to a BC value:

SELECT 
    TO_CHAR(date '-2030-12-30', 'DD-MM-YYYY AD')
FROM DUAL;

Result:

30-12-2030 BC

The resulting indicator is BC, even though my format string is AD.

Uppercase vs Lowercase

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

SELECT 
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY AD') AS "Uppercase",
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY ad') AS "Lowercase",
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY Ad') AS "Mixed"
FROM DUAL;

Result:

       Uppercase        Lowercase            Mixed 
________________ ________________ ________________ 
30-12-2030 AD    30-12-2030 ad    30-12-2030 Ad   

Dots vs No Dots

You can include dots if you prefer:

SELECT 
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY A.D.') AS "Uppercase",
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY a.d.') AS "Lowercase",
    TO_CHAR(date '2030-12-30', 'DD-MM-YYYY A.d.') AS "Mixed"
FROM DUAL;

Result:

         Uppercase          Lowercase              Mixed 
__________________ __________________ __________________ 
30-12-2030 A.D.    30-12-2030 a.d.    30-12-2030 A.D.    

Although, notice that this affected the mixed case indicator.

Today’s Date & Time

Here, I pass SYSDATE to use the current date:

SELECT 
    TO_CHAR(SYSDATE, 'DD-MM-YYYY A.D.')
FROM DUAL;

Result:

29-08-2021 A.D.