Below is a SQL of various examples of Oracle date formats, which I have found to be a useful reference.

Select

SYSDATE,

TO_CHAR(SYSDATE,’YYYY-MM-DD AD’) AS ANNO_DOMINI,

TO_CHAR(SYSDATE,’YYYYMMDD’) AS SORT_PATTERN1,

TO_CHAR(SYSDATE,’YYYY/MM/DD’) AS DATE_PATTERN1,

TO_CHAR(SYSDATE,’DD/MM/YYYY’) AS DATE_PATTERN2,

TO_CHAR(SYSDATE,’DD/MON/YYYY’) AS DATE_PATTERN3,

TO_CHAR(SYSDATE,’DD-MON-YY’) AS DATE_PATTERN4,

TO_CHAR(SYSDATE,’CC’) AS CENTURY,

TO_CHAR(SYSDATE,’YYYY’) AS FOUR_DIGIT_YEAR,

TO_CHAR(SYSDATE,’YY’) AS YEAR_OF_CENTURY,

TO_CHAR(SYSDATE,’J’) AS JUALIAN_DATE,

TO_CHAR(SYSDATE,’Q’) AS CALENDAR_QUARTER_OF_YEAR,

TO_CHAR(SYSDATE,’WW’) AS CALENDAR_WEEK_OF_YEAR,

TO_CHAR(SYSDATE,’DDD’) AS NUMBER_OF_CALENDAR_DAY_OF_YEAR,

TO_CHAR(SYSDATE,’W’) AS WEEK_OF_MONTH,

TO_CHAR(SYSDATE,’DAY’) AS NAME_OF_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’DY’) AS ABBREVIATED_DAY_OF_WEEK,

TO_CHAR(SYSDATE,’MM’) AS NUMERIC_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MON’) AS ABBREVIATED_MONTH_OF_YEAR,

TO_CHAR(SYSDATE,’MONTH’) AS SPELLED_OUT_MONTH_OF_YEAR

FROM DUAL;

Related References

• Oracle Database – Useful links
• Oracle SQL – How to limit the number of rows returned by a select query
• Oracle SQL – Removing Non-Numeric Characters
• Oracle SQL – Trimming a Specific Literal from a Field