How to Use CAST Function: 5 Tricky SQL Queries

The CAST in SQL means, converting from one data type to another. Looking into usage, in analytics, you would need data in another data type. For instance, Numeric in Char, Char in Varchar, and Timestamp in Varchar. Thus, the below SQL queries will give you a clear idea of how to use Cast.

SQL CAST sample quereis

Here, double colon refers to the CAST function. This means it is a shortcut to the CAST function.

Query:1 

The Varchar (10) gets only the first 10 bytes of data from Timestamp. In reality, you have other functions to get Time from Timestamp. It is also one method you can use.

SELECT timestamp_column, CAST(timestamp_column  AS varchar(10))  
FROM date_time_types;  

Query:2 

The double-colon works the same as the CAST function. You will get Time from the Timestamp.

SELECT timestamp_column::varchar(10) 
FROM date_time_types; 

Query:3  

It writes numeric-column three times – original value, Integer, and Varchar of length 6.

SELECT numeric_column,
         CAST(numeric_column AS integer),
         CAST(numeric_column AS varchar(6))
FROM number_data_types;

Query: 4 

It fails and writes an error since you cannot convert CHAR to numeric.

SELECT CAST(char_column AS integer) 
FROM char_data_types;

Query: 5

Convert DATETIME to DATE

SELECT CAST(date_time_column AS date format 'yymmdd')  
FROM char_data_types; 

Conclusion

1. Check target data type length before casting
2. Cannot convert CHAR to Numeric using Cast

Keep Reading