Oracle SQL Date Formatting Examples

Oracle Database, SQL Date Formatting Examples, TO_CHAR

Oracle Database – SQL Date Formatting Examples

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

 

One thought on “Oracle SQL Date Formatting Examples

  1. Pingback: Infosphere Datastage – Useful Date Transformations | Scientia Compendium

Comments are closed.