To_Char, To_Number, To_Date Functions

The conversion functions convert a value from one data type to another.
Data type conversion:
Ø  The Explicit conversion functions are
o   TO_CHAR     à        To Character conversion
o   TO_DATE      à        To Date conversion
o   TO_NUMBER à      To Number conversion

To_CHAR conversion function:
Ø  This function can be used in two different flavors.
o   To_Char (number conversion)
o   To_Char(date conversion)
o   To_CHAR (Number conversion)
Syntax: To_Char(number, fmt, ‘nlsparams’)
Ø  Converts number of number data type to a value of varchar2 data type.
Ø  ‘fmt’ is the optional number format, that can be used.
Ø  The ‘nlsparms’ specifies the characters returned by the number format element.

To_char (date conversion)
Syntax: To_char(date, fmt,’nlsparams’)
Ø  Converts date of date data type to a value of varchar2 data type in the format specified.
Ø  ‘fmt’ is the optional date format, that can be used.
Ø  The ‘nlsparams’ specifies the language in which month and day names and abbreviations are returned.
Working with number format models:
Decimal Indicator: D à 99D99
Ø  It returns the specified position of the decimal character.
Ø  The default decimal delimiter is ‘.’
Ø  Only one decimal indicator can be specified in a number format model.
Ex:
  • Select 1234, to_char(1234,’9999D99’) from dual;
  • Select 1234, to_char(1234,’999D99’) from dual;
Scientific notation indicator: EEEE à 9.9 EEEE
Ø  Returns a numeric value using scientific notation.
Ex:

  • Select 1234, to_char(1234,’9.9EEEE’), to_char(1234,’9.99EEEE’) from dual;
Group separator: G à 9G999
Ø  Returns the specified position of the group separator
Ø  Multiple group separators can be specified
Ex:
·         Select 1234567, to_char(1234567,’99G99G999’) from dual;
·         Select sal, to_char(sal, ’99G999D99’) from emp;
Local Currency Indicator: L à L999 OR 999L
Ø  It Returns the specified position of the local currency symbol.
Ex:
·         Select 1234, to_char(1234,’L9999’) from dual;
·         Select sal, to_char(sal,’L999999’) currency from emp;
·         Select Sal, to_char(sal,’L99G999D99’,’NLS_CURRENCY = IndRupees’) sal from emp;
Trailing Minus Indicator: MI à 9999MI
Ø  It returns negative value with a trailing minus sign.
Ø  It returns positive value with a trailing blank.
Ø  ‘MI’ format should be declared as trailing argument only.

Ex:
·         Select -1000, to_Char(-1000,’L99G999D99MI’) from dual;
·         Select sal, comm, to_char(comm – sal,’L99999MI’) from emp;
Negative Number Indicator: PR à 9999PR
Ø  Returns negative number in ‘<>’
Ø  It can appear only as trailing declaration.
Ex:
·         Select to_char(-1000,’L99G999D99PR’) from dual;
·         Select sal,comm, to_char(comm –sal,’L99G999D99PR’) from emp;
Roman Number Indicator: RN or rn
Ø  RN à returns upper roman number.
Ø  rn  à returns lower roman number.
Ø  The value can be an integer between 1 and 3999.
Ex:
·         Select 1000, to_char(1000,’RN’), to_char(1000,’rn’) from dual;
Sign Indicator: S à S99999 OR 99999S
Ø  Returns negative value with a leading minus sign.
Ø  Returns positive value with a leading plus sign.
Ø  ‘S’ can appear as first or last value.
Ex:
·         Select 1000, to_char(1000,’S9999’) from dual;
·         Select to_char(1000,’9999S’), to_char(-1000,’9999S’) from dual;
·         Select sal, to_char(sal,’S99999’),to_char(sal,’99999S’) from emp;
·         Select sal, comm, to_char(comm – sal,’S99999’), to_char(comm – sal,’99999S’) from emp;
Hexadecimal Indicator: X à XXXX
Ø  Returns the hexadecimal value of the specified number of digits.
Ø  If the number is not an integer, oracle rounds it to an integer.
Ø  Accepts only positive values OR 0.
Ex:
·         Select 1000, to_char(1000,’XXXX’) from dual;
·         Select ename, sal, to_char(sal,’xxxxx’) hexsal from emp;
Group Separator: , à 9,999
Ø  Returns a comma in the specified position.
Ø  Multiple commas can be specified.
Ex:
·         Select 10000, to_char(10000,’99,999.99’) from dual;
·         Select ename, sal, to_char(sal, ’99,999.99’) from emp;
Decimal Indicator:  . à 99.99
Ø  Returns a decimal point, at the specified position.
Ø  Only one period can be specified in a number format model.
Ex:
·         Select 10000, to_char(10000,’L99,999.99’) from dual;
·         Select Ename, Sal, to_char(sal,’L99,999.99’) from emp;
Dollar Indicator: $ à $9999
Ø  Returns value with a leading dollar sign.
Ex:
·         Select 10000, to_char(10000,’$99,999.99’) from dual;
·         Select ename, sal, to_char(sal,’$99,999.99’) from emp;

Zero Indicator: 0 à 0999 OR 9990
Ø  Returns leading OR trailing zeros.
Ex:
·         Select 10000,to_char(1000,’0999999’), to_char(1000,’09999990’) from dual;
·         Select ename, sal, to_char(sal,’$099,999.99’) from emp;
Digit place Marker: 9 à 9999
Ø  Returns value with a specified number of digits with a leading space when positive or leading minus when negative.
Ex:
·         Select 1000, 600, to_char(1000-600,’99999’), to_char(600-1000,’99999’) from dual;
·         Select 20.25, 20, to_char(20.25 – 20,’99999’) from dual;
ISO Currency Indicator: C à C999
Ø  Returns specified position of the ISO currency symbol.
Ex:
·         Select 1000,to_char(1000,’C9999.99’) from dual;
·         Select ename, sal, to_char(sal,’C9999.99’) from emp;
Date Format Models:
Ø  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 appear twice, and format items that represent similar information cannot be combined.
Ø  Capitalization In a spelled word, abbreviation, or Roman numeral follows capitalization in the corresponding format element.
Ø  Punctuation such as hyphens, slashes, commas, periods and colons.
AD or A.D / BC or B.C Indicator:
Ø  Indicates AD/BC with or without periods.
Ex:
·         Select sysdate, to_char(sysdate,’AD’) from dual;
·         Select to_char(sysdate,’B.C.’), to_char(sysdate,’A.D.’) from dual;
·         Select ename, sal, hiredate, to_char(hiredate,’A.D.’) from emp;
Meridian Indicator: AM or A.M. / PM or P.M.
Ø  It indicates meridian indicator with or without periods.
Ex:
·         Select sysdate, to_char(sysdate, ’A.M.’), to_char(sysdate,’PM’) from dual;
·         Select ename, sal, hiredate, to_char(hiredate,’AM’) from emp;
Century Indicator: CC/SCC
Ø  Indicates the century, S prefixes BC Date with ‘-‘.
Ex:
·         Select sysdate, to_char(sysdate,’CC-AD’) from dual;
·         Select sal, hiredate, to_char(hiredate,’SCC-AD’) from emp;
Numeric week Day Indicator: D à (1 – 7)
Ø  Returns the week day number
Ex:
·         Select sysdate,to_char(sysdate,’D’) from dual;
·         Select Ename, hiredate, to_char(hiredate, ‘D’) from emp;
Week Day spelling Indicator: à Day
Ø  Pads to a length of 9 characters.
Ex:
·         Select sysdate, to_char(sysdate,’DAY’) from dual;
·         Select sal, hiredate, to_char(hiredate,’DAY’) from emp where to_char(hiredate,’DAY’) =’WEDNESDAY’;
Month Day Indicator: DD
Ø  It indicates the day of the month(1-31)
Ex:
·         Select sysdate, to_char(sysdate,’DD-DAY’) from dual;
·         Select hiredate, to_char(hiredate,’DD-DAY’) from emp;
·         Select hiredate, to_char(hiredate,’DD-DAY’) from emp where to_char(hiredate,’DD-DAY’) = ’03-WEDNESDAy’);
Year Day Indicator: DDD
Ø  It indicates the day of the year (1 – 366)
Ex:
·         Select sysdate, to_char(sysdate,’DDD’) from dual;
·         Select Ename, hiredate, to_char(hiredate,’DDD’) from emp where to_char(hiredate,’DAY’) =’WEDNESDAY’;
Abbreviated Week Day: DY
Ø  It indicates the abbreviated name of the week day.
Ex:
·         Select sysdate, to_char(sysdate,’D-DY-DAY’) from dual;
·         Select ename, hiredate, to_char(hiredate,’D-DY-DAY’) from emp;
ISO Standard Year Week Indicator: IW
Ø  Specifies the week of the year (1 – 52 or 1 – 53) based on the ISO standard.
Ex:
·         Select sysdate, to_char(sysdate,’IW’) from dual;
·         Select ename, hiredate, to_char(hiredate,’IW’) from emp;

ISO Standard 4 Digit Year Indicator: IYYY
Ø  Specifies 4 digits year based on the ISO standard.
Ø  It can even be used in combination of IYY, IY, I.
Ex:
·         Select sysdate, to_char(sysdate,’IYYY’) from dual;
·         Select ename, hiredate, to_Char(hiredate,’IYYY’) from emp where to_char(hiredate,’DAY’) =’WEDNESDAY’;
Four Digit Year Indicators: YYYY OR SYYYY
Ø  Returns four digit year, ‘S’ prefixes BC dates with ‘-‘.
Ø  It can even be used in combination of YYY or YY or Y.
Ø  Y, YYY returns year with comma in that position.
Ex:
·         Select sysdate, to_char(sysdate,’YYYY’) four, to_char(sysdate,’YYY’) three from dual;
·         Select ename, hiredate, to_char(hiredate,’YYYY’) from emp;
Spelled year Indicator: YEAR OR SYEAR
Ø  Returns the numerical year in spelling.
Ø  ‘S’ prefixes BC Dates with ‘-‘.
Ex:
·         Select sysdate, to_Char(sysdate,’YEAR’) from dual;
·         Select Ename, hiredate, to_char(hiredate,’YEAR’) from emp;
Week of the month Indicator: W
Ø  Specifies the week of the month (1 - 5).
Ø  Week starts on the first day of the earth month and ends on the seventh day.
Ex:
·         Select sysdate, to_char(sysdate,’W’) from dual;
·         Select ename, hiredate, to_char(hiredate,’W’) from emp;
Year Week Indicator: WW
Ø  Specifies the week of the year (1 – 53).
Ø  Week 1 starts on the first day of the year and continues to the seventh day in that year.
Ex:
·         Select sysdate, to_char(sysdate,’WW’) from dual;
·         Select ename, hiredate, to_char(hiredate,’WW’) from emp;
Quarter of the Year Indicator: Q
Ø  Returns the quarter of the year.
Ø  Quarter starting with the month of January and ending with every three months.
Ex:
·         Select sysdate, to_char(sysdate,’Q’)  from dual;
·         Select ename, hiredate, to_char(hiredate,’Q’) From emp where to_char(hiredate,’Q’) = 4;
Julian Day Indicator: J
Ø  Returns the Julian day of the given date.
Ø  It is the number of days since January 1, 4712 BC.
Ø  Numbers specified with ‘J’ must be integers.
Ex:
·         Select sysdate, to_char(sysdate,’J’) from dual;
·         Select Ename, to_char(hiredate,’J-DDD-DD-D’) From emp;
Numeric Month Indicator: MM
Ø  Returns the numeric abbreviation of the month.
Ex:
·         Select sysdate, to_char(sysdate,’MM-YYYY’) from dual;
·         Select ename, hiredate, to_char(hiredate,’DD-MM-YYYY’) From emp where to_char(hiredate,’MM’) = 12;
Abbreviated Month Indicator: MON
Ø  Returns the abbreviated name of the month.
Ex:
·         Select sydate, to_char(sysdate,’MM-MON’) from dual;
Month Spelling Indicator: MONTH
Ø  Spells the name of the month, padded to a length of 9 characters.
Ex:
·         Select sysdate, to_char(sysdate,’MON-MONTH’) From dual;
·         Select ename, hiredate, to_char(hiredate,’MONTH, YYYY’) from emp;
Twelve Hour Clock Mode: HH or HH12
Ø  Returns the hour of the day in twelve hour clock mode.
Ex:
·         Select sysdate, to_char(sysdate,’HH’), to_char(sysdate,’HH12, AM’) from dual;
·         Select ename, hiredate, to_char(hiredate,’HH12 : AM’) from emp;
Twenty Hour Clock Mode: HH24
Ø  Returns the hour of the day in twenty four hour clock mode. (0 – 23)
Ex:
·         Select sysdate, to_char(sysdate,’HH24’) from dual;
Minutes Indicator: MI
Ø  Returns the minutes from the given date (0 – 59).
Ex:
·         Select sysdate, to_char(sysdate,’MI’), to_char(sysdate,’HH:MI’) from dual;
·         Select Ename, sal, to_char(hiredate,’HH:MI’) from emp where job =’CLERK’;
Roman Month Indicator: RM
Ø  Returns the roman numerical month (I – XII).
Ex:
·         Select sysdate, to_char(sysdate,’RM’), to_char(sysdate,’DD-RM-YY’) from dual;
·         Select ename, sal, to_char(hiredate,’DD-RM-YY’) From emp;
Seconds Indicator: SS
Ø  Returns seconds from the given date (0 – 59).
Ex:
·         Select sysdate, to_char(sysdate,’SS’), to_char(sysdate,’HH:MI:SS’) from dual;
·         Select sysdate, to_char(sysdate,’DD-MONTH-YYYY, HH:MI:SS A.M.’) from dual;
·         Select ename, sal, hiredate, to_char(hiredate,’HH24:MI:SS’) from emp;
Seconds Past Mid Night: SSSSS
Ø  Display seconds past midnight (0 – 86399).
Ex:
·         Select sysdate, to_char(sysdate,’SSSSS’) from dual;
Date Format Punctuators:
Ø  The Punctuation marks that can be used in date formats are…
Ø  ‘-‘, ‘/’, ‘!’, ‘.’ , ‘;’, ‘:’, “text”
Date Format Elements Suffixes: TH OR SP
TH à Suffixes the Ordinal number with ‘ST’ or ‘ND’ or ‘RD’ or ‘TH’.
Example: DDTH à 20TH.
Ex:
·         Select sysdate, to_char(sysdate,’DDTH,MONTH,YYYY’) from dual;
·         Select ename, sal, hiredate, to_char(hiredate,’DDTH,MONTH,YYYY’) from emp;
Sp à Spells Ordinal Numbers.
Example: DDSP à TWENTY
·         Select sysdate, to_char(sysdate,’DDSP, Month, YYY’) from dual;
·         Select ename, sal, to_char(hiredate,’DDSP,Month,YYYY’) from emp;
·         Select sysdate, to_char(sysdate,’DDSPTH, Month,YYYY’) from dual;
·         Select ename, sal, to_char(hiredate,’DDSPTH, Month, YYYY’) from emp;
·         Select sysdate, to_char(sysdate,’DDSPTH Month YYYYSP’) from dual;
Date format Elements Restrictions:
Ø  The suffixes when added to date return values always in English.
Ø  Date suffixes are valid only on output, hence cannot be used to insert a date into the database.
Format Model Modifiers:
Fill Mode Indicator: FM
Ø  It suppresses blank padding in the return value of the to_char function.
Format Exact: FX
Ø  It specifies exact matching for the character argument and date format model.
Ex:
·   Select sysdate, to_char(sysdate,’DDSPTH MONTH YYYYSP’), to_char(sysdate,’FMDDSPTH MONTH YYYYSP’) from dual;
TO_NUBMER Function:
Syntax: TO_NUBMER (char, fmt, ‘nlsparam’)
Ø  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.
Ex:
·         Select ’$10,000.00’, to_number(’$10,000.00’,’L99,999.99’) from dual;

·         Select ’$1,000.00’ to_number(’$1,0000.00’,’L9,999.99’) + 500 from dual;


TO_DATE Function:
Syntax: TO_DATE(char, fmt, ‘nlsparam’)
Ø  Converts given char of CHAR or VARCHAR2 data type to a value of DATE data type.
Ø  The ‘fmt’ is an optional date format specifying the format of char.
Ex:
·         Select ename, hiredate, add_months(to_date(’17-DEC-1980’,’DD-MON-YY’),3) from emp where hiredate = ’17-DEC-1980’;
·         Select ename, hiredate,add_months(to_date(’1980-DECEMBER-17’,’YYYY-MONTH-DD’),3) from emp where hiredate = ’17-DEC-1980’;
·         Select ename, hiredate, add_months(to_date(’1980-DECEMBER-17’,’YYYY-MONTH-DD’),3) from emp where to_char(hiredate,’FMYYYY-MONTH-DD’) =’1980-DECEMBER-17’);
·         Select ’12-August-2007’,to_date(’12-August-2007’,’DD-Month-YYYY’) + 3 from dual;

Let Us Revisit INSERT Statement Once Again:
Working With Invalid Numbers:
Ex:
·         Create table sampins
(
SampleNum number(6),
SampleDate Date
);
·         Insert into sampins(SampleNum,SampleDate) alues(to_number(’1,23,456’,’9G99G999’), sysdate);
·         Insert into sampins(SampleNum, SampleDate) values(to_number(’1,23,457-‘,’9G99G999MI’),sysdate);

Working With Invalid Dates:
Ex:
·         Insert into sampins(SampleNum, SampleDate) values(123458,’02-AUG-07’);
·         Insert into sampins(SampleNum, SampleDate) values(123459,sysdate);
·         Insert into Sampins(SampleNum, SampleDate) values(123460, to_date(’02-August-2007,06:45:36 P.M.’,’DD-Month-YYYY,HH:MI:SS P.M.’);
Spelling a Number:
Ex:

·         Select to_char(to_date(’&GiveNumber’,’J’),’JSP’) “splled number” from dual;