totn MySQL Functions

MySQL: CAST Function

This MySQL 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
The value to convert to another datatype.
type

The datatype that you wish to convert value to. It can be one of the following:

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);
Result: '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);
Result: '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);
Result: '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);
Result: '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);
Result: -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);
Result: 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);
Result: '4'

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