TO_CHAR in MySQL

SQL | Conversion Function

Improve Article

Save Article

Like Article

• Difficulty Level : Easy
• Last Updated : 29 Sep, 2022

Read

Discuss

Courses

Practice

Video

Improve Article

Save Article

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).
 

FromToVARCHAR2 or CHARNUMBERVARCHAR2 or CHARDATEDATEVARCHAR2NUMBERVARCHAR2

1. QUERY:
    

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

1. OUTPUT :
    

Employee_IDFIRST_NAMESALARY100Steven24000101Neena17000102lex17000

1. QUERY:
    

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

1. OUTPUT :
    

Employee_IDFIRST_NAMESALARY100Steven24000101Neena17000102lex17000

1. 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_IDMONTH_HIRED20506/94

Elements of the Date Format Model :
 

YYYYFull year in NumbersYEARYear spelled outMMTwo digit value for monthMONTHFull name of the monthMONThree Letter abbreviation of the monthDYThree letter abbreviation of the day of the weekDAYFull Name of the weekDDNumeric 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.
 

ELEMENTDESCRIPTIONAM or PMMeridian indicatorA.M. or P.M.Meridian indicator with periodsHH or HH12 or HH24Hour of day,or hour (1-12),or hour (0-23)MIMinute 0-59SSSecond 0-59SSSSSSecond past Mid Night 0-86399

Other Formats :
 

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

Specifying Suffixes to Influence Number Display :
 

ELEMENTDESCRIPTIONTHOrdinal Number (for example DDTH for 4THSPSpelled out number (for example DDSP for FOURSPTH 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 :
 

LASTNAMEHIIREDATEAustin25 January 2005Shubham20 June 2004Nishant15 January 1999Ankit15 July 1995Vanshika5 August 2004Kusum10 June 1994Faviet11 March 2005King9 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 number0Forces a zero to be displayed$places a floating dollar signLUses 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 employee_id,first_name,salary
FROM employees
WHERE salary > '15000';

0

OUTPUT :
 

LASTNAMEHIREDATEKumar24-MAY-99

 

My Personal Notes arrow_drop_up

Save

Please Login to comment...

What is TO_CHAR?

What does TO_CHAR Q mean in SQL?

How to convert date to char in MySQL?

How to convert int to char in MySQL?