Syntax:
CONVERT(expr USING transcoding_name), CONVERT(expr,type)

The CONVERT() function takes an expression of any type and produces a
result value of the specified type.

CONVERT(... USING ...) is standard SQL syntax. The non-USING form of
CONVERT() is ODBC syntax.

CONVERT(expr USING transcoding_name) converts data between different
character sets. In MySQL, transcoding names are the same as the
corresponding character set names. For example, this statement converts
the string 'abc' in the default character set to the corresponding
string in the utf8mb4 character set:

SELECT CONVERT('abc' USING utf8mb4);

CONVERT(expr, type) syntax (without USING) takes an expression and a
type value specifying the result type. This operation may also be
expressed as CAST(expr AS type), which is equivalent. These type values
are permitted:

o BINARY[(N)]

  Produces a string with the BINARY data type. For a description of how
  this affects comparisons, see
  https://dev.mysql.com/doc/refman/8.0/en/binary-varbinary.html. If the
  optional length N is given, BINARY(N) causes the cast to use no more
  than N bytes of the argument. Values shorter than N bytes are padded
  with 0x00 bytes to a length of N.

o CHAR[(N)] [charset_info]

  Produces a string with the CHAR data type. If the optional length N
  is given, CHAR(N) causes the cast to use no more than N characters of
  the argument. No padding occurs for values shorter than N characters.

  With no charset_info clause, CHAR produces a string with the default
  character set. To specify the character set explicitly, these
  charset_info values are permitted:

  o CHARACTER SET charset_name: Produces a string with the given
    character set.

  o ASCII: Shorthand for CHARACTER SET latin1.

  o UNICODE: Shorthand for CHARACTER SET ucs2.

  In all cases, the string has the character set default collation.

o DATE

  Produces a DATE value.

o DATETIME

  Produces a DATETIME value.

o DECIMAL[(M[,D])]

  Produces a DECIMAL value. If the optional M and D values are given,
  they specify the maximum number of digits (the precision) and the
  number of digits following the decimal point (the scale).

o DOUBLE

  Produces a DOUBLE result. Added in MySQL 8.0.17.

o FLOAT[(p)]

  If the precision p is not specified, produces a result of type FLOAT.
  If p is provided and 0 <= < p <= 24, the result is of type FLOAT. If
  25 <= p <= 53, the result is of type DOUBLE. If p < 0 or p > 53, an
  error is returned. Added in MySQL 8.0.17.

o JSON

  Produces a JSON value. For details on the rules for conversion of
  values between JSON and other types, see
  https://dev.mysql.com/doc/refman/8.0/en/json.html#json-comparison.

o NCHAR[(N)]

  Like CHAR, but produces a string with the national character set. See
  https://dev.mysql.com/doc/refman/8.0/en/charset-national.html.

  Unlike CHAR, NCHAR does not permit trailing character set information
  to be specified.

o REAL

  Produces a result of type REAL. This is actually FLOAT if the
  REAL_AS_FLOAT SQL mode is enabled; otherwise the result is of type
  DOUBLE.

o SIGNED [INTEGER]

  Produces a signed integer value.

o spatial_type

  As of MySQL 8.0.24, CAST() and CONVERT() support casting geometry
  values from one spatial type to another, for certain combinations of
  spatial types. The following list shows the permitted type
  combinations, where "MySQL extension" designates casts implemented in
  MySQL beyond those defined in the SQL/MM standard:

  o From Point to:

    o MultiPoint

    o GeometryCollection

  o From LineString to:

    o Polygon (MySQL extension)

    o MultiPoint (MySQL extension)

    o MultiLineString

    o GeometryCollection

  o From Polygon to:

    o LineString (MySQL extension)

    o MultiLineString (MySQL extension)

    o MultiPolygon

    o GeometryCollection

  o From MultiPoint to:

    o Point

    o LineString (MySQL extension)

    o GeometryCollection

  o From MultiLineString to:

    o LineString

    o Polygon (MySQL extension)

    o MultiPolygon (MySQL extension)

    o GeometryCollection

  o From MultiPolygon to:

    o Polygon

    o MultiLineString (MySQL extension)

    o GeometryCollection

  o From GeometryCollection to:

    o Point

    o LineString

    o Polygon

    o MultiPoint

    o MultiLineString

    o MultiPolygon

  In spatial casts, GeometryCollection and GeomCollection are synonyms
  for the same result type.

  Some conditions apply to all spatial type casts, and some conditions
  apply only when the cast result is to have a particular spatial type.
  For information about terms such as "well-formed geometry," see
  https://dev.mysql.com/doc/refman/8.0/en/geometry-well-formedness-vali
  dity.html.

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-general-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-point-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-linestring-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-polygon-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-multipoint-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-multilinestring-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-multipolygon-conditions

  o https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html#spatial
    -cast-geometrycollection-conditions

  General Conditions for Spatial Casts

  These conditions apply to all spatial casts regardless of the result
  type:

  o The result of a cast is in the same SRS as that of the expression
    to cast.

  o Casting between spatial types does not change coordinate values or
    order.

  o If the expression to cast is NULL, the function result is NULL.

  o Casting to spatial types using the JSON_VALUE() function with a
    RETURNING clause specifying a spatial type is not permitted.

  o Casting to an ARRAY of spatial types is not permitted.

  o If the spatial type combination is permitted but the expression to
    cast is not a syntactically well-formed geometry, an
    ER_GIS_INVALID_DATA
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_gis_invalid_data) error occurs.

  o If the spatial type combination is permitted but the expression to
    cast is a syntactically well-formed geometry in an undefined
    spatial reference system (SRS), an ER_SRS_NOT_FOUND
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_srs_not_found) error occurs.

  o If the expression to cast has a geographic SRS but has a longitude
    or latitude that is out of range, an error occurs:

    o If a longitude value is not in the range (−180, 180], an
      ER_GEOMETRY_PARAM_LONGITUDE_OUT_OF_RANGE
      (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-refer
      ence.html#error_er_geometry_param_longitude_out_of_range) error
      occurs.

    o If a latitude value is not in the range [−90, 90], an
      ER_GEOMETRY_PARAM_LATITUDE_OUT_OF_RANGE
      (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-refer
      ence.html#error_er_geometry_param_latitude_out_of_range) error
      occurs.

    Ranges shown are in degrees. If an SRS uses another unit, the range
    uses the corresponding values in its unit. The exact range limits
    deviate slightly due to floating-point arithmetic.

  Conditions for Casts to Point

  These conditions apply when the cast result type is Point:

  o If the expression to cast is a well-formed geometry of type Point,
    the function result is that Point.

  o If the expression to cast is a well-formed geometry of type
    MultiPoint containing a single Point, the function result is that
    Point. If the expression contains more than one Point, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection containing only a single Point, the function
    result is that Point. If the expression is empty, contains more
    than one Point, or contains other geometry types, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than Point, MultiPoint, GeometryCollection, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to LineString

  These conditions apply when the cast result type is LineString:

  o If the expression to cast is a well-formed geometry of type
    LineString, the function result is that LineString.

  o If the expression to cast is a well-formed geometry of type Polygon
    that has no inner rings, the function result is a LineString
    containing the points of the outer ring in the same order. If the
    expression has inner rings, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type
    MultiPoint containing at least two points, the function result is a
    LineString containing the points of the MultiPoint in the order
    they appear in the expression. If the expression contains only one
    Point, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type
    MultiLineString containing a single LineString, the function result
    is that LineString. If the expression contains more than one
    LineString, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection, containing only a single LineString, the
    function result is that LineString. If the expression is empty,
    contains more than one LineString, or contains other geometry
    types, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than LineString, Polygon, MultiPoint, MultiLineString, or
    GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to Polygon

  These conditions apply when the cast result type is Polygon:

  o If the expression to cast is a well-formed geometry of type
    LineString that is a ring (that is, the start and end points are
    the same), the function result is a Polygon with an outer ring
    consisting of the points of the LineString in the same order. If
    the expression is not a ring, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs. If the
    ring is not in the correct order (the exterior ring must be
    counter-clockwise), an ER_INVALID_CAST_POLYGON_RING_DIRECTION
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_polygon_ring_direction) error occurs.

  o If the expression to cast is a well-formed geometry of type
    Polygon, the function result is that Polygon.

  o If the expression to cast is a well-formed geometry of type
    MultiLineString where all elements are rings, the function result
    is a Polygon with the first LineString as outer ring and any
    additional LineString values as inner rings. If any element of the
    expression is not a ring, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs. If any
    ring is not in the correct order (the exterior ring must be
    counter-clockwise, interior rings must be clockwise), an
    ER_INVALID_CAST_POLYGON_RING_DIRECTION
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_polygon_ring_direction) error occurs.

  o If the expression to cast is a well-formed geometry of type
    MultiPolygon containing a single Polygon, the function result is
    that Polygon. If the expression contains more than one Polygon, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection containing only a single Polygon, the function
    result is that Polygon. If the expression is empty, contains more
    than one Polygon, or contains other geometry types, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than LineString, Polygon, MultiLineString, MultiPolygon, or
    GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to MultiPoint

  These conditions apply when the cast result type is MultiPoint:

  o If the expression to cast is a well-formed geometry of type Point,
    the function result is a MultiPoint containing that Point as its
    sole element.

  o If the expression to cast is a well-formed geometry of type
    LineString, the function result is a MultiPoint containing the
    points of the LineString in the same order.

  o If the expression to cast is a well-formed geometry of type
    MultiPoint, the function result is that MultiPoint.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection containing only points, the function result is a
    MultiPoint containing those points. If the GeometryCollection is
    empty or contains other geometry types, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than Point, LineString, MultiPoint, or GeometryCollection, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to MultiLineString

  These conditions apply when the cast result type is MultiLineString:

  o If the expression to cast is a well-formed geometry of type
    LineString, the function result is a MultiLineString containing
    that LineString as its sole element.

  o If the expression to cast is a well-formed geometry of type
    Polygon, the function result is a MultiLineString containing the
    outer ring of the Polygon as its first element and any inner rings
    as additional elements in the order they appear in the expression.

  o If the expression to cast is a well-formed geometry of type
    MultiLineString, the function result is that MultiLineString.

  o If the expression to cast is a well-formed geometry of type
    MultiPolygon containing only polygons without inner rings, the
    function result is a MultiLineString containing the polygon rings
    in the order they appear in the expression. If the expression
    contains any polygons with inner rings, an
    ER_WRONG_PARAMETERS_TO_STORED_FCT
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_wrong_parameters_to_stored_fct) error occurs.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection containing only linestrings, the function result
    is a MultiLineString containing those linestrings. If the
    expression is empty or contains other geometry types, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than LineString, Polygon, MultiLineString, MultiPolygon, or
    GeometryCollection, an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to MultiPolygon

  These conditions apply when the cast result type is MultiPolygon:

  o If the expression to cast is a well-formed geometry of type
    Polygon, the function result is a MultiPolygon containing the
    Polygon as its sole element.

  o If the expression to cast is a well-formed geometry of type
    MultiLineString where all elements are rings, the function result
    is a MultiPolygon containing a Polygon with only an outer ring for
    each element of the expression. If any element is not a ring, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs. If any
    ring is not in the correct order (exterior ring must be
    counter-clockwise), an ER_INVALID_CAST_POLYGON_RING_DIRECTION
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_polygon_ring_direction) error occurs.

  o If the expression to cast is a well-formed geometry of type
    MultiPolygon, the function result is that MultiPolygon.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection containing only polygons, the function result is
    a MultiPolygon containing those polygons. If the expression is
    empty or contains other geometry types, an
    ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  o If the expression to cast is a well-formed geometry of type other
    than Polygon, MultiLineString, MultiPolygon, or GeometryCollection,
    an ER_INVALID_CAST_TO_GEOMETRY
    (https://dev.mysql.com/doc/mysql-errors/8.0/en/server-error-referen
    ce.html#error_er_invalid_cast_to_geometry) error occurs.

  Conditions for Casts to GeometryCollection

  These conditions apply when the cast result type is
  GeometryCollection:

  o GeometryCollection and GeomCollection are synonyms for the same
    result type.

  o If the expression to cast is a well-formed geometry of type Point,
    the function result is a GeometryCollection containing that Point
    as its sole element.

  o If the expression to cast is a well-formed geometry of type
    LineString, the function result is a GeometryCollection containing
    that LineString as its sole element.

  o If the expression to cast is a well-formed geometry of type
    Polygon, the function result is a GeometryCollection containing
    that Polygon as its sole element.

  o If the expression to cast is a well-formed geometry of type
    MultiPoint, the function result is a GeometryCollection containing
    the points in the order they appear in the expression.

  o If the expression to cast is a well-formed geometry of type
    MultiLineString, the function result is a GeometryCollection
    containing the linestrings in the order they appear in the
    expression.

  o If the expression to cast is a well-formed geometry of type
    MultiPolygon, the function result is a GeometryCollection
    containing the elements of the MultiPolygon in the order they
    appear in the expression.

  o If the expression to cast is a well-formed geometry of type
    GeometryCollection, the function result is that GeometryCollection.

o TIME

  Produces a TIME value.

o UNSIGNED [INTEGER]

  Produces an unsigned integer value.

o YEAR

  Produces a YEAR value. Added in MySQL 8.0.22. These rules govern
  conversion to YEAR:

  o For a four-digit number in the range 1901-2155 inclusive, or for a
    string which can be interpreted as a four-digit number in this
    range, return the corresponding YEAR value.

  o For a number consisting of one or two digits, or for a string which
    can be interpeted as such a number, return a YEAR value as follows:

    o If the number is in the range 1-69 inclusive, add 2000 and return
      the sum.

    o If the number is in the range 70-99 inclusive, add 1900 and
      return the sum.

  o For a string which evaluates to 0, return 2000.

  o For the number 0, return 0.

  o For a DATE, DATETIME, or TIMESTAMP value, return the YEAR portion
    of the value. For a TIME value, return the current year.

    If you do not specify the type of a TIME argument, you may get a
    different result from what you expect, as shown here:

mysql> SELECT CONVERT("11:35:00", YEAR), CONVERT(TIME "11:35:00", YEAR);
+---------------------------+--------------------------------+
| CONVERT("11:35:00", YEAR) | CONVERT(TIME "11:35:00", YEAR) |
+---------------------------+--------------------------------+
|                      2011 |                           2020 |
+---------------------------+--------------------------------+

  o If the argument is of type DECIMAL, DOUBLE, DECIMAL, or REAL, round
    the value to the nearest integer, then attempt to cast the value to
    YEAR using the rules for integer values, as shown here:

mysql> SELECT CONVERT(1944.35, YEAR), CONVERT(1944.50, YEAR);
+------------------------+------------------------+
| CONVERT(1944.35, YEAR) | CONVERT(1944.50, YEAR) |
+------------------------+------------------------+
|                   1944 |                   1945 |
+------------------------+------------------------+
1 row in set (0.00 sec)

mysql> SELECT CONVERT(66.35, YEAR), CONVERT(66.50, YEAR);
+----------------------+----------------------+
| CONVERT(66.35, YEAR) | CONVERT(66.50, YEAR) |
+----------------------+----------------------+
|                 2066 |                 2067 |
+----------------------+----------------------+
1 row in set (0.00 sec)

  o An argument of type GEOMETRY cannot be converted to YEAR.

  o For a value that cannot be successfully converted to YEAR, return
    NULL.

  A string value containing non-numeric characters which must be
  truncated prior to conversion raises a warning, as shown here:

mysql> SELECT CONVERT("1979aaa", YEAR);
+--------------------------+
| CONVERT("1979aaa", YEAR) |
+--------------------------+
|                     1979 |
+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------+
| Level   | Code | Message                                   |
+---------+------+-------------------------------------------+
| Warning | 1292 | Truncated incorrect YEAR value: '1979aaa' |
+---------+------+-------------------------------------------+

URL: https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

