What are different Conversion functions in SQL?

Read this Oracle Tutorial Point article to know what are different DATE Conversion functions in SQL. The conversion functions convert a specific value from one data type to another data type. There are two different types of Data Type conversion in Oracle. That are,
  • Implicit Datatype Conversion
  • Explicit Datatype Conversion
Implicit Datatype Conversion:
Implicit Datatype conversion work according to the conversion specified by Oracle. The assignment succeeds if the oracle servers can convert the datatype of the value. CHAR to NUMBER conversion succeed only if the character strings represents a valid NUMBER. If the character strings represents the default format DD-MON-YY then only CHAR to DATE conversions succeed.

        IN ASSIGNMENT                                         IN EXPRESSION
=====================================================================
  • Char / Varchar2 ==> Number                      Char / Varchar2 ==> Number 
  • Char / Varchar2 ==> Date                           Char / Varchar2 ==> Date
  • Number ==> Varchar2
  • Date ==> Varchar2
Explicit Datatype Conversion:
SQL provides 3 different functions to convert a specific value from one datatype to another datatype. The functions are,
  • To_Char         ==>    To Convert into Characters
  • To_Date         ==>    To Convert into Date
  • To_Number   ==>    To Convert into Number
To_Char Conversion Function:
This To_Char conversion function can be used in 2 different flavours.
  1. To_Char(Number Conversion)
  2. To_Char(Date Conversion)
1. To_Char(Number Conversion):
The To_Char(Number Conversion) function can be used to convert number of number datatype to a value of Varchar2 datatype. In the below syntax 'FMT' is the option number format that can be used and the 'nlsparams' specifies the character returned by the number format element.

Syntax:     To_Char(Number,FMT,'nlsparams')

2. To_Char(Date Conversion):
The To_Char(Date Conversion) function can be used to converts Date of DATE datatype to a value of VARCHAR2 datatype in the format specified. In the below syntax, 'FMT' is the optional DATE format that can be used and the 'nlsparams' specifies the language in which and day names and abbreviations are returned.

Syntax:     To_Char(DATE,FMT,'nlsparams')

Comments