MySQL: CAST Function

This tutorial explains how to use the MySQL CAST function with syntax and examples.

Description

The MySQL CAST function converts a value from one datatype to another datatype.

Syntax

The syntax for the CAST function in MySQL is:

CAST( value AS type )

Parameters or Arguments

value
type

Value	Description
DATE	Converts value to DATE type, which has a date portion only. Format is 'YYYY-MM-DD'. Supported range is '1000-01-01' to '9999-12-31'.
DATETIME	Converts value to DATETIME type, which has both date and time portions. Format is 'YYYY-MM-DD HH:MM:SS'. Supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
TIME	Converts value to TIME type, which has a time portion only. Format is 'HH:MM:SS'. Supported range is '-838:59:59' to '838:59:59'.
CHAR	Converts value to CHAR type, which is a fixed length string.
SIGNED	Converts value to SIGNED type, which is a signed 64-bit integer.
UNSIGNED	Converts value to UNSIGNED type, which is an unsigned 64-bit integer.
BINARY	Converts value to BINARY type, which is a binary string.

Note

Starting in MySQL 4.0.6, you can use CHAR as type in the CAST function.
You can use the BINARY function as shorthand for CAST(value AS BINARY).
See also CONVERT function.

Applies To

The CAST function can be used in the following versions of MySQL:

MySQL 5.7, MySQL 5.6, MySQL 5.5, MySQL 5.1, MySQL 5.0, MySQL 4.1, MySQL 4.0.2

Example

Let's look at some MySQL CAST function examples and explore how to use the CAST function in MySQL.

With DATE

The first CAST function example shows how to cast a value to a DATE type. For example:

mysql> SELECT CAST('2014-02-28' AS DATE);
Output: '2014-02-28'

This CAST example takes the value '2014-02-28' and casts it as a DATE datatype.

With DATETIME

This CAST function example shows how to cast a value to a DATETIME type. For example:

mysql> SELECT CAST('2014-02-28 08:14:57' AS DATETIME);
Output: '2014-02-28 08:14:57'

This CAST example takes the value '2014-02-28 08:14:57' and casts it as a DATETIME datatype.

With TIME

This CAST function example shows how to cast a value to a TIME type. For example:

mysql> SELECT CAST('08:14:57' AS TIME);
Output: '08:14:57'

This CAST example takes the value '08:14:57' and casts it as a TIME datatype.

With CHAR

This CAST function example shows how to cast a value to a CHAR type. For example:

mysql> SELECT CAST(125 AS CHAR);
Output: '125'

This CAST example takes the value 125 and casts it as a CHAR datatype with the value of '125'.

With SIGNED

This CAST function example shows how to cast a value to a SIGNED type. For example:

mysql> SELECT CAST(4-6 AS SIGNED);
Output: -2

This CAST example takes the value 4-5 and casts it as a SIGNED datatype with the value of -2.

With UNSIGNED

This CAST function example shows how to cast a value to an UNSIGNED type. For example:

mysql> SELECT CAST(4-6 AS UNSIGNED);
Output: 18446744073709551614

This CAST example takes the value 4-5 and casts it as an UNSIGNED datatype with the value of 18446744073709551614.

With BINARY

This CAST function example shows how to cast a value to a BINARY type. For example:

mysql> SELECT CAST('4' AS BINARY);
Output: '4'

This CAST example takes the value '4' and casts it as a BINARY datatype with the binary string value of '4'.

Mysql Cast Function