Using the TO_CHAR Function with Numbers

TO_CHAR(number, 'format_model')

• These are some of the format elements that you can use with the TO_CHAR function to display a number value as a character :
• SELECT empno, TO_CHAR(sal, '$99,999.00') as Month_Hired FROM emp;

Using the TO_NUMBER and TO_DATE Functions :

Convert a character string to a number format using the TO_NUMBER function:

• TO_NUMBER(char[, 'format_model'])

Convert a character string to a date format using the TO_DATE function:

• TO_DATE(char[, 'format_model'])
  

Using the TO_CHAR Function with Dates

TO_CHAR(date, 'format_model')

The format model:
– Must be enclosed by single quotation marks
– Is case-sensitive
– Can include any valid date format element
– Has an fm element to remove padded blanks or suppress leading zeros
– Is separated from the date value by a comma

Example:
SELECT empno, TO_CHAR(hiredate, 'MM/YY') as Month_Hired FROM emp

Elements of the Date Format Model :
Time elements format the time portion of the date:
HH24:MI:SS AM 15:45:32 PM

Add character strings by enclosing them in double quotation marks:
DD "of" MONTH 12 of OCTOBER

Number suffixes spell out numbers: (Not Yet Implemented)
ddspth fourteenth

Example: SELECT empno, TO_CHAR(hiredate, 'ddspth') Month_Hired FROM emp;

Elements of the Date Format Model :

• Use the formats that are listed in the following tables to display time information and literals and to change numerals to spelled numbers
• SELECT empno, TO_CHAR(hiredate, 'fmDD Month YYYY') As Month_Hired FROM emp;
• The SQL statement in the slide displays the last names and hire dates for all the employees. The hire date appears as 17 December 1980.