• Conversion function are used to convert one data type value into another data type.

to_number():

  • It converts character value in to the numeric data type.

Syntax:

to_number (char);

Example:

SELECT to_number (substr (‘$150’, 2, 3) FROM DUAL;

Output:

150

to_char():

  • It converts numeric data type in to the character data type.

Syntax:

to_char (no, format) ;

Example:

SELECT to_char (15345,’$0999, 99’) FROM DUAL ;

Output:

$0153, 45

 to_char():

  • It returns date in to the specify format.
  • If no format is specified then date will be return in the default format ‘DD-MON-YY’.

Syntax:

to_char(date, format);

Example:

SELECT to_char(sysdate, ’DD-MONTH-YYYY’) FROM DUAL ;

Output:

01-NOVEMBER-2016
  • The following format available for the date.
  • MON:      Display month in three letters.
  • Example:   Jan, May
  • Month:    Display complete month name.
  • Example:  January, May
  • MM: –       Display month in digit.
  • Example: 01, 02
  • YY: –        Displays year in four digit.
  • Example: 13, 12
  • YYYY: –    It displays year in four digits.
  • Example: 2013, 2014
  • DDTH:     Display date with postfix as first, second, third.
  • Example:  1st, 2nd, 3ed
  • DDSP:      Display date with spelling.
  • HH:           Display hours.