Open In App
Related Articles

Conversion Function in SQL

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Report issue
Report

When you define expressions and local variables then you should specify what type of data will be stored in those objects such as text data, money, dates, numbers, or characters.

  • Strings Data types such as CHAR and VARCHAR.
  • Decimal values such as FLOAT and REAL.
  • Binary String such as BINARY.
  • Date and Time Data Types such as DATE, TIME, TIMESTAMP, and DATETIME.
  • Numeric Data types such as INT, DOUBLE, and BIGINT.
  • MS Access Data Types such as TEXT, LONG, and BYTE.
    On the basis of this, there are two types of conversion in the Data first implicit types conversion and the second is explicit data type conversion. In implicit type conversion Server can automatically convert the data type from one type to another (i.e., VARCHAR TO CHAR and INT TO FLOAT) but in explicit data type conversion it can be done by the user side.

sql-conversion-function1

Implicit Data-Type Conversion

In this type of conversion, the data is converted from one type to another implicitly (by itself/automatically).

FromTo
VARCHAR2 or CHARNUMBER
VARCHAR2 or CHARDATE
DATEVARCHAR2
NUMBERVARCHAR2

Query

create table employees(
employee_id INT PRIMARY KEY ,
first_name VARCHAR(50) ,
salary INT);
INSERT INTO employees(employee_id,first_name,salary)
VALUES
(100,'Steven',24000),
(101,'Neena',17000),
(102,'Lex',17000),
(103,'John',11000),
(104,'Robert',12000),
(105,'Leo',10000);

1. Query

Here, we want to retrieve the employee_id, first_name, and salary from the employees table whose salary is greater than 15000 then the query is

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > 15000;

1. Output

img

2. Query

SELECT employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

2. Output

img

Here we see the output of both queries came out to be the same, in spite of the 2nd query using ‘15000’ as text, it is automatically converted into an int data type.

Explicit Data-Type Conversion

In this type of conversion, the data is converted from one type to another explicitly (by the user). simply we can say, users define the type to which the expression is to be converted.

explicit_conversion

TO_CHAR Function

TO_CHAR function is used to typecast a numeric or date input to a character type with a format model (optional).

Syntax

TO_CHAR(number1, [format], [nls_parameter])

Using the TO_CHAR Function with Dates

Syntax

TO_CHAR(date, ’format_model’)


The format model:

  • Must be enclosed in single quotation marks and is case sensitive
  • Can include any valid date format element in the query
  • Has an fm element to remove padded blanks or suppress leading zeros
  • Is separated from the date value by a comma

Example :

SELECT employee_id, TO_CHAR(hire_date, ’MM/YY’) Month_Hired
FROM employees
WHERE last_name = ’Higgins’;


Output :

EMPLOYEE_IDMONTH_HIRED
20506/94


Elements of the Date Format Model

YYYYFull-year in Numbers
YEARYear spelled out

YY

Two-digit value of year

MMTwo-digit value for the month
MONTHFull name of the month
MONThree Letter abbreviation of the month

D

Number of Days in a Week

DYThree-letter abbreviation of the day of the week
DAYFull Name of the Day
DDNumeric day of the month

Date Format Elements – Time Formats
Use the formats listed in the following tables to display time information and literals and to change numerals to spelled numbers.

ELEMENTDESCRIPTION
AM or PMMeridian indicator
A.M. or P.M.Meridian indicator with periods
HH or HH12 or HH24Hour of day, or hour (1-12), or hour (0-23)
MIMinute 0-59
SSSecond 0-59
SSSSSSecond past Mid Night 0-86399


Other Formats

ELEMENTDESCRIPTION
/ . ,Punctuation is reproduced in the result
“of the”The quoted string is reproduced in the result


Specifying Suffixes to Influence Number Display

ELEMENTDESCRIPTION
THOrdinal Number (for example DDTH for 4TH
SPSpelled outnumber (for example DDSP for FOUR
SPTH or THSPspelled out ordinal numbers (for example DDSPTH for FOURTH


Example :

SELECT last_name,
TO_CHAR(hire_date, ’fmDD Month YYYY’)
AS HIREDATE
FROM employees;


Output :

LASTNAMEHIIREDATE
Austin25 January 2005
Shubham20 June 2004
Nishant15 January 1999
Ankit15 July 1995
Vanshika5 August 2004
Kusum10 June 1994
Faviet11 March 2005
King9 April 1996

Using the TO_CHAR Function with Numbers

Syntax

TO_CHAR(number, ’format_model’)


These are some of the format elements you can use with the TO_CHAR function to display a number value as a character :

9Represent a number
0Forces a zero to be displayed
$places a floating dollar sign
LIt uses the floating local currency symbol
.Print a decimal point
,Prints a Thousand indicator

Example :
 

SELECT TO_CHAR(salary, ’$99,999.00’) SALARY
FROM employees
WHERE last_name = ’Ernst’;


Output :

SALARY
$5000

Using the TO_NUMBER and TO_DATE Functions :

Convert a character string to a number format using the TO_NUMBER function :
 

TO_NUMBER(char[, ’format_model’])


Convert a character string to a date format using the TO_DATE function:
 

TO_DATE(char[, ’format_model’])


These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function.
Example :

SELECT last_name, hire_date
FROM employees
WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);


Output :

LASTNAMEHIREDATE
Kumar24-MAY-1999


 



Last Updated : 28 Aug, 2023
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads