One of the commonly used functions in RDBMS is the CAST function, which helps to transform the expression of one data type to another data type. It’s also available in Apache Hive and Apache Impala.
The below are the scenarios when we use CAST function.
1) If the date information has to be converted to a string.
2) If an integer value has to be translated to a string.
3) If the date in BIGINT has to be converted to the right date format,
4) If two integer values are to be combined (without summing up).
5) If the value of the currency has to be translated to an integer or a string.
Examples:
SELECT CAST(99.99 AS INT);
This returns 99 as CAST function converted the decimal value to INT.
SELECT CAST(SUBSTR(‘A99.99’,2,5) AS DECIMAL(4.2));
Returns 99.99
SELECT CAST(1574984632061 AS TIMESTAMP);
Returns 2019-11-28 15:43:52.061
SELECT CONCAT(CAST(1 AS STRING), ”, CAST(0 AS STRING));
Returns 10
SELECT CAST(‘2020-02-02’ AS TIMESTAMP);
Returns 2020-02-02 00:00:00
Big Data and SQL 
4 comments