Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Conversion Functions in SQL
There may be times when the SQL query was expecting some data type but it got another. In that case type conversion is done by SQL. This is implicit type conversion. However, sometimes the programmer explicitly converts one data type into another in a query. This is known as explicit type conversion. Both of these in detail are given as follows:
Implicit Data Type Conversion
In implicit data type conversion, the SQL programmer does not specify anything. Rather the system converts one type of data to another as per its requirements. For example - a numeric data type can be converted to character if required and vice versa.
Implicit DataType Conversion only occurs if the data types used in the query are valid so that the system can recognise them and convert them as required. This is not possible with invalid or wrongly provided data types.
Explicit Data Type Conversion
If the programmer wants,they can explicitly convert data types from one form to another. There are SQL functions provided for this express purpose. These SQL functions are −
TO_CHAR
This function is used to explicitly convert a number or date data type to char.
The syntax of this function is given as follows −
TO_CHAR(number,format,parameters)
This function changes number to char with the specific format as provided according to the syntax. The parameters can be used to specify decimal characters, group separators etc.
For example −
SELECT CHAR(sysdate, “Month DD,YYYY”) FROM DUAL;
This returns the system date in the form of 31 July, 2018
TO_NUMBER
This function is used to explicitly convert a string to number. If the string to be converted does not contain numeric characters then TO_NUMBER shows an error.
The syntax of this function is given as follows −
TO_NUMBER(number,format,parameters)
This function changes the given string to number with the specific format as provided according to the syntax.
The parameters and format are optional so to convert a string to a number
For example,
TO_NUMBER (‘353.78’);
This returns the string 353.78 in numerical format.
TO_DATE
This function takes character values and returns the output in the date format.
The syntax of this function is given as follows −
TO_DATE(number, format, parameters)
This function changes the given string to number with the specific format as provided according to the syntax.
SELECT TO_DATE(‘2018/07/31’,’yyyy/mm/dd’) FROM DUAL;
This takes the values in character format and returns them in date format as specified.
1K+ Views
