› › ›

Type Conversion Functions

Functions for converting an expression of one data type to another.

`CAST`

Synopsis:

CAST(
    expression 
 AS data_type)

	Expression to cast. If `null`, the function returns `null`.
	Target data type to cast to

Description: Casts the result of the given expression to the target data type. If the cast is not possible (for example because of target type is too narrow or because the value itself cannot be converted), the query fails.

SELECT CAST('123' AS INT) AS int;

      int
---------------
123

SELECT CAST(123 AS VARCHAR) AS string;

    string
---------------
123

SELECT YEAR(CAST('2018-05-19T11:23:45Z' AS TIMESTAMP)) AS year;

     year
---------------
2018

Both ANSI SQL and Elasticsearch SQL types are supported with the former taking precedence. This only affects FLOAT which due naming conflict, is interpreted as ANSI SQL and thus maps to double in Elasticsearch as oppose to float. To obtain an Elasticsearch float, perform casting to its SQL equivalent, real type.

`CONVERT`

Synopsis:

CONVERT(
    expression, 
    data_type)

	Expression to convert. If `null`, the function returns `null`.
	Target data type to convert to

Description: Works exactly like CAST with slightly different syntax. Moreover, apart from the standard data types it supports the corresponding ODBC data types.

SELECT CONVERT('123', SQL_INTEGER) AS int;

      int
---------------
123

SELECT CONVERT('123', INTEGER) AS int;

      int
---------------
123

© 2023-2025 Elasticsearch
As of September 2024, Elasticsearch is available under a choice of three licenses: the Server Side Public License (SSPL), the Elastic License, or the AGPLv3 (OSI approved).
Elasticsearch and the Elasticsearch logo are trademarks of Elasticsearch B.V., registered in the U.S. and in other countries.
https://www.elastic.co/guide/en/elasticsearch/reference/8.17/sql-functions-type-conversion.html