SQL: How to Use CAST Function in Analytics

The term CAST came from engineering words. It means converting from one data type to another. In Data Science, during analytics, there is an occasion where you need conversion to get data in another data type. You can convert Numeric to Char or Char to Varchar. Here are the best CAST function SQL queries.

How to Use CAST Function in Analytics
How to Use CAST Function in Analytics

CAST Function

Here are Sample SQL Queries

Here, double colon refers to CAST function. 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; 

Takeaways

  1. Always check target datatype length before you use CAST
  2. You cannot convert CHAR to numeric value.

Keep Reading

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.