Conversion function

Implicit conversion:-

A varchar2 or char value can be implicitly converted to
Number or date type value by oracle.
Similarly, a number or data type can be automatically
Converted to character data by oracle server.
For example the below select queries .both the queries will
Give the same output because oracle internally treats 10000
And '10000' are same.

Query-1
select employee_id,first_name,last_name,salary
From employees
Where salary > 10000;

Query-2

YYYY Full year in numbers
YEAR Year spelled out (in English)
MM Two-digit value for month
MONTH Full name of the month
MON Three-letter abbreviation of the month
DY Three-letter abbreviation of the day of the week
DAY Full name of the day of the week
DD Numeric day of the month

To_char functions
Element Result
9 Represents a number
0 Forces a zero to be displayed
$ Places a floating dollar sign
L Uses the floating local currency symbol
. Prints a decimal point
, Prints a comma as thousands indicator


select employee_id,first_name,last_name,salary
from employees
where salary > '10000';

Explicit data type conversion are single row function which are capable of

typecasting column value,literal or expression.
To_char,To_number,To_date are three functions which perform
change of data types.
1. To_char()
This function is used to typecast a numeric value or date to character value with

a format model.
Syntax
To_char(number1,[format],[nls_paramete])
Consider the below select query.The query format the Hire_date & salary columns of

employees table using To_char function.
select first_name,To_char(hire_date,'Month DD,YYYY') hire_date,To_char

(salary,'$99999.99') salary
from employees
where rownum < 5;

select to_char(sysdate,'Month') from dual;

Elements Of the Date Model Function.







The To_number function converts a character value to a numeric data type.
if the string being converted contains non numeric characters, the function return an error.
Syntax
To_number(string1,[format],[nls_parameter])

SELECT TO_NUMBER('129.24', '9G999D99')
FROM DUAL;
TO_NUMBER('129.24','9G999D99')
------------------------------
129.24

To_date Function

This function takes character values as input & return formatted date
Equivalent of the same.
To_date function allows users to enter a date in any format, then it converts the entry
Into the default format by oracle server.

Syntax:-To_date(strin1,[format_mask],[nls_language])

SELECT TO_DATE('July 15, 1987, 09:00 A.M.', 'Month dd, YYYY, HH:MI A.M.', 'NLS_DATE_LANGUAGE = American')
FROM DUAL
/