| by Arround The Web | No comments

SQL Cast Function

“In this tutorial, we will learn how to use the CAST() function in Standard SQL to convert a value from one data type to another.”

Let us explore.

Function Definition

The function syntax is as shown:

CAST (expr AS target_type)

The function arguments are as follows:

  1. expr – defines the value or expression you wish to convert to another type.
  2. 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.

SELECT
    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:

INT        FLOAT     num
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.

SELECT
    CAST('2022-10-10' AS DATE) AS var1;

Output:

Var1
2022-10-10

Example 3 – Using String to Datetime

The example below uses the cast() function to convert the value to datetime.

SELECT
CAST('2020-10-10 16:54:21' AS DATETIME) AS str_to_datetime;

Result:

str_to_datetime
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.

SELECT
CAST(0 AS BOOL) AS f,
CAST(5 AS BOOL) AS t,
CAST(1 AS bool) AS t;

Result:

f           t           t_1
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.

Share Button

Source: linuxhint.com

Leave a Reply