[role="xpack"]
[testenv="basic"]
[[sql-functions-type-conversion]]
=== Type Conversion Functions

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

[[sql-functions-type-conversion-cast]]
==== `CAST`

.Synopsis:
[source, sql]
----
CAST(
    expression <1>
 AS data_type) <2>
----

<1> Expression to cast. If `null`, the function returns `null`.
<2> Target data type to cast to

*Description*: Casts the result of the given expression to the target <<sql-data-types, 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.

[source, sql]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[conversionStringToIntCast]
----

[source, sql]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[conversionIntToStringCast]
----

[source, sql]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[conversionStringToDateTimeCast]
----

IMPORTANT: Both ANSI SQL and {es-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 {es} as oppose to `float`.
To obtain an {es} `float`, perform casting to its SQL equivalent, `real` type.


[[sql-functions-type-conversion-convert]]
==== `CONVERT`

.Synopsis:
[source, sql]
----
CONVERT(
    expression, <1>
    data_type)  <2>
----

<1> Expression to convert. If `null`, the function returns `null`.
<2> Target data type to convert to

*Description*: Works exactly like <<sql-functions-type-conversion-cast>> with slightly different syntax.
Moreover, apart from the standard <<sql-data-types, data types>> it supports the corresponding
https://docs.microsoft.com/en-us/sql/odbc/reference/appendixes/explicit-data-type-conversion-function?view=sql-server-2017[ODBC data types].

[source, sql]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[conversionStringToIntConvertODBCDataType]
----

[source, sql]
----
include-tagged::{sql-specs}/docs/docs.csv-spec[conversionStringToIntConvertESDataType]
----
