SQL Cast Function
Let us explore.
Function Definition
The function syntax is as shown:
The function arguments are as follows:
- expr – defines the value or expression you wish to convert to another type.
- target_type – sets the target data type to which you wish to convert. Accepted data types include int64, numeric, bignumeric, float64, bool, string, bytes, data, datetime, array, struct, time, and timestamp.
The function returns the input expression as the target data, data type.
Example Usage
The following example shows how to convert from strings to various data types.
CAST('100' AS int64) AS INT,
CAST('3.14159' AS float64) AS FLOAT,
CAST('132' AS NUMERIC) AS num;
This should return the values as follows:
100 3.14159 132
Example 2 – Convert String to Date
The example below shows how to use the cast() function to convert a string to a date type.
CAST('2022-10-10' AS DATE) AS var1;
Output:
2022-10-10
Example 3 – Using String to Datetime
The example below uses the cast() function to convert the value to datetime.
CAST('2020-10-10 16:54:21' AS DATETIME) AS str_to_datetime;
Result:
2020-10-10T16:54:21
Example 2 – Converting Int to Bool
In the example below, the cast() function allows us to convert the int 0 to FALSE and the int 1 and above to TRUE.
CAST(0 AS BOOL) AS f,
CAST(5 AS BOOL) AS t,
CAST(1 AS bool) AS t;
Result:
FALSE TRUE TRUE
Conclusion
In this post, we covered the basics of working with the cast() function in Standard SQL to convert a given expression to another data type.
Thanks for reading. Check out our other SQL tutorials to learn more.
Source: linuxhint.com