Conversion Functions in SQL

Conversion Functions in SQL are used to convert the Datatype in the Result-Set.

Conversion Functions in SQL are two types Implicit data type conversion and Explicit Datatype Conversion.

 

Implicit Data Type Conversion

For assignments, Oracle can automatically convert the following.

VARCHAR2 –> Number

VARCHAR2 –> Date

Number     –> VARCHAR2

Date            –> VARCHAR2

 

Explicit Data Type Conversion

To perform the explicit conversion we need to use conversion functions. We have three Explicit Functions.

  • To_Char

  • To_Date

  • To_Number

Conversion Function in SQL

 

Using TO_Char Functions with Dates

Syntax : to_char (date, ‘format_model’)

 

The Format Model :

  • Must be enclosed in single quotation marks

  • Is Case-Sensitive

  • Can include any valid date format element

  • Has a ‘fm’ element to remove padded blanks or suppress leading Zeros

  • Is separated from the date value by a comma

 

Elements of the Date Format Model

  1. YYYY –  Full Year in Numbers

  2. YEAR – Year Spelled out in English

  3. MM – Two digit value of Month

  4. MONTH – Full Name of Month

  5. MON – Three letter abbreviation of the Month

  6. DAY – Full name of Day of the week

  7. DD – Numeric day of the Month

SQL> Select First_Name , To_char(hire_date, ‘fmDD MON YYYY’) as HIREDATE from Employees;

Date to Char Conversion in SQL

 

You can see the date format is changed as per our query in Result-Set for more you can visit Dates in SQL Tutorial. Please try some other formats in your local.

 

Using To_Char Functions with Numbers

Syntax : to_char (number, ‘format_model’)

 

Elements of the number Format Model

  1. 0 –  Forces a Zero to be displayed

  2. $ – Places a floating dollar sign

  3. L – Uses the floating local currency symbol

  4. .  – Prints a Decimal Point

  5. , – Print a comma as thousand indicator

 

SQL> Select First_Name, to_char(Salary, ‘$99,999.00’) as Salary from Employees;

Number to Char Conversion in SQL

0 Comment

Leave a Reply