- 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.