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.
AD or A.D / BC or B.C:
CC / SCC:
D ==> Day of the Week (1-7)
Examples:
Day ==> Indicates spelled name of the Week Day
DD ==> It indicates the Day of the Month(1-31)
Examples:
Examples:
DY ==> It indicates the abbreviated name of the day.
Examples:
IW ==> Specifies the week of the year (1-52 or 1-53) based on the ISO standard.
Examples:
IYYY ==> Specifies 4 digits year based on the ISO standard. Similarly IYY, IY.
Examples:
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:
YEAR / SYEAR ==> Returns the spelled out year.
Examples:
W ==> Specified the week of the Month(1-5). Weeks starts on the first day of the month and ends with the seventh.
Examples:
WW ==> Specifies the week of the year(1-54). Week starts on the first day of the Month and ends on the seventh.
Examples:
Q ==> Returns the quarter of the Year.
Examples:
J ==> Returns the JULIAN DAY.
MM ==> Returns the digit numeric abbreviation of the Month.
Examples:
MON ==> Returns the abbreviated name of the Month.
Examples:
MONTH ==> spells the name of the month, padded to a length of 9 characters.
Examples:
HH / HH12 ==> Returns the Hour of the day in twelve hour clock mode.
Examples:
HH24 ==> Returns the Hour of the day in twenty four hour clock mode. (0-23)
Examples:
MI ==> Returns the Minutes from the given date (0-59)
Examples:
RM ==> Returns the Roman numeral Month (I - XII)
Examples:
SS ==> Returns seconds from the given date (0-59)
Examples:
SSSS ==> Display seconds past midnight (0-86399)
Examples:
Date Format Element Suffixes:
TH ==> Specifies the Ordinal number.
Examples: DDTH 20th
SP ==> Spells Numbers
Examples: DDSP ==> TWENTY
Spelled Ordinal number ==> Spells Numbers
Examples: DDSPTH ==> TWENTIETH
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:
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:
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:
Specification Examples:
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.
AD or A.D / BC or B.C:
- Indicates AD/BC with or without periods.
Examples:
AM or A.M. / PM or P.M.: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;
- It indicates meridian indicator with or without periods.
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,
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.
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
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.
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’)
- 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.
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;
This comment has been removed by the author.
ReplyDelete