AD/BC indicators and AM/PM Indicators

Oracle provides two formats, AD and BC, to characterize a year.  However, they both serve the same purpose, and we can use either of them with equivalent results. If we have used the format BC in our query, and the date we are applying this format to comes out to be an AD year, Oracle is intelligent enough to print AD instead of BC, and vice versa. 

For example:

SELECT TO_CHAR(SYSDATE, 'YYYY AD'),  TO_CHAR(SYSDATE, 'YYYY BC') FROM DUAL;

Another Example:

SELECT TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY AD'),      

TO_CHAR(ADD_MONTHS(SYSDATE,-50000), 'YYYY BC') FROM DUAL;

AM/PM indicators

The AM/PM indicators (as well as A.M. and P.M.) behave exactly the same as the AD/BC indicators. If we have used the AM format in our query, and the time we are applying this format to comes out to be a PM time, Oracle is intelligent enough to print PM instead of AM, and vice versa. 

For example:

SELECT TO_CHAR(SYSDATE, 'HH:MI:SS AM'),      

TO_CHAR(SYSDATE, 'HH:MI:SS PM'),      

TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS AM'),     

TO_CHAR(SYSDATE - 8/24, 'HH:MI:SS PM') FROM DUAL;

MINUTES: MI or MM

Many SQL beginners assume that since HH represents hours and SS represents sec- onds, MM would represent minutes, and try to write the following SQL queries to print the current time:

SELECT TO_CHAR(SYSDATE, 'HH:MM:SS') FROM DUAL;

RR Year format

With the RR year format, the first two digits of the specified year are determined based upon the last two digits of the current year and the last two digits of year specified.

The following rules apply:

• If the specified year is less than 50, and the last two digits of the current year are less than 50, then the first two digits of the return date are the same as the first two digits of the current date.

• If the specified year is less than 50, and the last two digits of the current year are greater than or equal to 50, then first two digits of the return date are 1 greater than the first two digits of the current date.

• If the specified year is greater than 50, and the last two digits of the current year are less than 50, then first two digits of the return date are 1 less than the first two digits of the current date.

• If the specified year is greater than 50, and the last two digits of the current year are greater than or equal to 50, then the first two digits of the return date are the same as the first two digits of the current date.

The following example demonstrates these rules:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

SELECT SYSDATE, TO_CHAR(SYSDATE,'DD-MON-YYYY') FROM DUAL;