DATE format models in Oracle SQL

Read this oracle tutorila point article to know more about different DATE format models in SQL. The Date format models can be used in the TO_CHAR function to translate a DATE value from original format to user format. The total length of a date format model cannot exceed 22 characters.

Date Format Elements:
A date format model is composed of one or more date format elements.
  • For input format models, format items cannot seem double and format items that represent similar information cannot be combined.
Capitalization in a spelled out word, abbreviation or Roman numeral follows capitalization in the corresponding format elements. Punctuations such are hyphens, slashes, commas, periods and colons.

AD or A.D / BC or B.C:
  • Indicates AD/BC with or without periods.
Examples:
SQL> SELECT TO_CHAR(SYSDATE,'AD') FROM DUAL;
SQL> SELECT TO_CHAR(SYSDATE,'B.C'), TO_CHAR(SYSDATE,'A.D') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'A.D') FROM EMP;
AM or A.M. / PM or P.M.:
  • It indicates meridian indicator with or without periods.
Examples:
SQL> SELECT TO_CHAR(SYSDATE,'A.M.'),TO_CHAR(SYSDATE,'PM') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'AM') FROM EMP;

CC / SCC:
  • Indicates the century, S prefixes BC date with,
Examples:
SQL> SELECT TO_CHAR(SYSDATE,'SCC-AD') FROM DUAL;
SQL> SELECT SAL, HIREDATE, TO_CHAR(HIREDATE,'SCC-AD') FROM EMP;

D ==> Day of the Week (1-7)

Examples:
SQL> SELECT TO_CHAR(SYSDATE,'D') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'D') FROM EMP;

Day ==> Indicates spelled name of the Week Day
  • Pads to a length of 9 characters.
Examples:
SQL> SELECT TO_CHAR(SYSDATE,'DAY') FROM DUAL;
SQL> SELECT SAL, HIREDATE,TO_CHAR(HIREDATE,'DAY') FROM EMP WHERE TO_CHAR(HIREDATE,'DAY')='WEDNESDAY';

DD ==> It indicates the Day of the Month(1-31)

Examples:
SQL> SELECT TO_CHAR(SYSDATE,'DD-DAY') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'DD-DAY') FROM EMP;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'DD-DAY') FROM EMP WHERE TO_CHAR(HIREDATE,'DD-DAY')='03-WEDNESDAY';
  • DAY ==> Specifies in Upper Case
  • DAY ==> Specifies in Initcap Case
  • DAY ==> Specifies in Lower Case
DDD ==> It indicates the Day of the Year (1-366);

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'DDD') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'DDD') FROM EMP WHERE TO_CHAR(HIREDATE,'DAY')='WEDNESDAY';

DY ==> It indicates the abbreviated name of the day.

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'D-DY-DAY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'D-DY-DAY') FROM EMP WHERE DNO IN(10,20);

IW ==> Specifies the week of the year (1-52 or 1-53) based on the ISO standard.

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'IW') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE, 'IW') FROM EMP;

IYYY ==> Specifies 4 digits year based on the ISO standard. Similarly IYY, IY.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'IYYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'IYYY') FROM EMP;

YYYY / SYYYY ==> Returns 4 digit year, S prefixes BC dates with ‘-’. Similarly YYY / YY / Y.
Y, YYY ==> Returns year with comma in this position.

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'YYYY') FOUR, TO_CHAR(SYSDATE,'YYY') THREE, TO_CHAR(SYSDATE,'Y,YYY') COMMA FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'YYYY') FROM EMP WHERE DNO=20;

YEAR / SYEAR ==> Returns the spelled out year.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'YEAR') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'YEAR') FROM EMP;

W ==> Specified the week of the Month(1-5). Weeks starts on the first day of the month and ends with the seventh.

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'W') FROM DUAL;
SQL> SELECT ENAME, HIREDATE, TO_CHAR(HIREDATE,'W') FROM EMP;

WW ==> Specifies the week of the year(1-54). Week starts on the first day of the Month and ends on the seventh.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'WW') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'WW') FROM EMP;

Q ==> Returns the quarter of the Year.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'Q') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'Q') FROM EMP;

J ==> Returns the JULIAN DAY.
  • It is the number of the day since January 1,4712 BC.
  • Numbers specified with ‘J’ must be integers.
Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'J') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TP_CHAR(HIREDATE,'J') FROM EMP;
SQL> SELECT ENAME,HIREDATE,TP_CHAR(HIREDATE,'J-DDD-DD-DD') FROM EMP;

MM ==> Returns the digit numeric abbreviation of the Month.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MM-YYYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'MM-YYYY') FROM EMP;

MON ==> Returns the abbreviated name of the Month.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MM-MON') FROM DUAL;
SQL> SELECT HIREDATE,TO_CHAR(HIREDATE,'MM-MON') FROM DUAL;

MONTH ==> spells the name of the month, padded to a length of 9 characters.

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'MM-MONTH') FROM DUAL;
SQL> SELECT HIREDATE, TO_CHAR(HIREDATE,'MM-MONTH') FROM EMP;

HH / HH12 ==> Returns the Hour of the day in twelve hour clock mode.

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'HH'),TO_CHAR(SYSDATE,'HH12,AM') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'HH12:AM') FROM EMP;

HH24 ==> Returns the Hour of the day in twenty four hour clock mode. (0-23)

Examples:
SQL> SELECT SYSDATE, TO_CHAR(SYSDATE,'HH24') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'HH24') FROM EMP WHERE DNO=10;

MI ==> Returns the Minutes from the given date (0-59)

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'MI'),TO_CHAR(SYSDATE,'HH:MI') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'MI'),TO_CHAR(HIREDATE,'HH:MI') FROM EMP WHERE DNO=10;

RM ==> Returns the Roman numeral Month (I - XII)

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'RM') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-RM-YY') FROM EMP;

SS ==> Returns seconds from the given date (0-59)

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'SS'),TO_CHAR(SYSDATE,'HH:MI:SS') FROM DUAL;
SQL> SELECT ENAME, HIREDATE,TO_CHAR(HIREDATE,'HH:MI:SS') FROM EMP WHERE DNO IN (10,20);

SSSS ==> Display seconds past midnight (0-86399)

Examples:
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'SSSS') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE,'DD-MM-YY, HH:MI:SS, SSSS') FROM EMP;
  • The punctuation marks that can be used in Date formats are (- ; / ; !; .; ; ; : ; ‘text’)
RR Date Format Elements:
  • The RR date format element is similar to the YY date format element.
  • The RR format element provides additional flexibility for storing date values in other Centuries.
  • The RR date format element allows to store the date to the previous as well as the text Centuries.
  • If the last 2 digits of the Century Year are:
                    0-49     50-99
0-49 Returns date is in the Current Century. the returns date is in the Preceding Century. 
50-59 Returns date in the next century. Returns date in the current century.

Date Format Element Suffixes:

TH ==> Specifies the Ordinal number.

Examples: DDTH 20th
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDTH, MONTH, YYY') FROM DUAL;
SQL> SELECT ENAME,HIREDATE,TO_CHAR(HIREDATE, 'DDTH, MONTH, YYYY') FROM EMP;

SP ==> Spells Numbers

Examples: DDSP ==> TWENTY
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSP, MONTH, YYYY') FROM DUAL;
SQL> SELECT ENAME,SAL,TO_CHAR(HIREDATE,'DDSP, MONTH, YYYY') FROM EMP;

Spelled Ordinal number ==> Spells Numbers

Examples: DDSPTH ==> TWENTIETH
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH, MONTH, YYYY') FROM DUAL;
SQL> SELECT ENAME,SAL,TO_CHAR(HIREDATE,'DDSPTH,MONTH,YYYY') FROM EMP;
SQL> SELECT SYSDATE,TO_CHAR(SYSDATE,'DDSPTH MONTH YYYYSP') FROM DUAL;

Date Format Elements Restrictions:

The suffixes when added to date return value always in English. Date suffixes are valid only on output, hence cannot be used to insert a date into the database.

Format Model Modifiers:
  • FM (Fill Mode) ==> it suppresses blank padding in the return value of the TO_CHAR function.
  • FX (Format Exact) ==> it specifies exact matching for the character argument and date format model.
Examples:
SQL> SELECT SYSDATE, TO_CHAR (SYSDATE,'DDSPTH MONTH YYYYSP'), TO_CHAR (SYSDATE,'FMDDSPTH MONTH YYYYSP') FROM DUAL;

TO_NUMBER Function:

In the below syntax it converts a char. Value of CHAR or VARCHAR2 data type containing a Number in the format specified by the optional format model fmt, to a value of NUMBER data type.

Syntax: TO_NUMBER(Char,fmt,’nlsparam’)

Examples:
SQL> SELECT TO_NUMBER('$10,000.00','L99,999.99') FROM DUAL;
SQL> SELECT TO_NUMBER('$1,000.00','L9,999.99') FROM DUAL;

TO_DATE Function:
In the below syntax, converts char of CHAR or VARCHAR2 datatype to a value of DATE data type. The fmt is a date format specifying format of char.

Syntax:/b> TO_DATE(Char,fmt,'nlsparam');

Examples:

SQL> SELECT ENAME, HIREDATE, ADD_MONTHS(TO_CHAR('17-DEC-1980','DD-MON-YY'),3) FROM EMP;

Specification Examples:

SQL> SELECT TO_CHAR(ADD_MONTHS(HIREDATE,1),'DD-MON-YYYY') "NEXT_MONTH" EMP WHERE ENAME='SMITH';
SQL> SELECT CONCAT(CONCAT(ENAME,' IS A '),JOB) DESIGNATION FROM EMP WHERE ENO=7900;
SQL> SELECT TRUNC(TO_DATE('01-OCT-90','DD-MON-YY'),YEAR) "NEW YEAR" FROM DUAL;
SQL> SELECT TO_CHAR(ADD_MONTHS(LAST_DAY(HIREDATE),5),'DD-MON-YYYY') "FIVE_MONTHS" FROM EMP WHERE ENAME='MARTIN';
SQL> SELECT MONTHS_BETWEEN(TO_DATE('06-06-2016','MM-DD-YYYY'),TO_DATE('01-01-2016','MM-DD-YYYY')) MONTHS FROM DUAL;
SQL> SELECT NEXT_DAY('15-JUN-2016','Wednesday') "NEXT DAY" FROM DUAL;
SQL> SELECT ENAME,NVL(TO_CHAR(COMM),'NOT APPLICABLE') "COMMISSION FROM EMP WHERE DNO=10;
SQL> SELECT ROUND(TO_DATE('14-06-2016','YEAR')) "NEW YEAR" FROM DUAL;
SQL> SELECT TO_CHAR(TO_DATE('14-JUN-16','DD-MON-RR'),'YYYY') YEAR FROM DUAL;

Comments