SQL | Conversion Function

In some cases, the Server uses data of one type where it expects data of a different data type. This can happen when the Server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Server, or explicitly by the user.

Implicit Data-Type Conversion :

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

From	To
VARCHAR2 or CHAR	NUMBER
VARCHAR2 or CHAR	DATE
DATE	VARCHAR2
NUMBER	VARCHAR2

EXAMPLE :

1. QUERY:

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

   OUTPUT :

   Employee_ID	FIRST_NAME	SALARY
   100	Steven	24000
   101	Neena	17000
   102	lex	17000

2. QUERY:

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

   OUTPUT :

   Employee_ID	FIRST_NAME	SALARY
   100	Steven	24000
   101	Neena	17000
   102	lex	17000

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

Explicit Data-Type Conversion :

TO_CHAR Function :

TO_CHAR function is used to typecast a numeric or date input to 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
• 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_ID	MONTH_HIRED
205	06/94

Elements of the Date Format Model :

YYYY	Full year in Numbers
YEAR	Year spelled out
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 of the week
DD	Numeric day of the month

Elements of the Date Format Model :

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.

ELEMENT	DESCRIPTION
AM or PM	Meridian indicater
A.M. or P.M.	Meridian indicater with periods
HH or HH12 or HH24	Hour of day,or hour (1-12),or hour (0-23)
MI	Minute 0-59
SS	Second 0-59
SSSSS	Second past Mid Night 0-86399

Other Formats :

ELEMENT	DESCRIPTION
/ . ,	Punctuation is reproduced in the result
“of the”	Quoted string is reproduced in the result

Specifying Suffixes to Influence Number Display :

ELEMENT	DESCRIPTION
TH	Ordinal Number (for example DDTH for 4TH
SP	Spelled out number (for example DDSP for FOUR
SPTH or THSP	spelled out ordinal numbers (for example DDSPTH for FOURTH

EXAMPLE :

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

OUTPUT :

LASTNAME	HIIREDATE
Austin	25 January 2005
Shubham	20 June 2004
Nishant	15 January 1999
Ankit	15 July 1995
Vanshika	5 August 2004
Kusum	10 June 1994
Faviet	11 March 2005
King	9 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 :

9	Represent a number
0	Forces a zero to be displayed
$	places a floating dollar sign
L	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 :

LASTNAME	HIREDATE
Kumar	24-MAY-99

---

---