For an instructor lead, in-depth look at learning SQL click below.
The purpose of the CAST and CONVERT functions in SQL is to allow data of one type to be displayed or used as another type. This type of operation is also usually referred to as a “type casting” or “type conversion”.
CAST Function
The CAST function converts a value (of any type) into a specified datatype.
SYNTAX
|
1 2 3 |
CAST (expression AS datatype(length)) |
Here, the expression can be a column or value that you want to convert and datatype(length) is the data type you want to convert expression into.
Example of CAST:
|
1 2 3 |
SELECT CAST(25.65 AS INT); |
This SQL code will convert the float 25.65 into an integer 25.
CONVERT Function
CONVERT function is essentially a more advanced version of the CAST function. It allows converting data from one type to another, but also formatting the output of the conversion.
SYNTAX
|
1 2 3 |
CONVERT(data_type(length),expression,style) |
Here, style is an optional parameter that determines how the resulting value is displayed.
Example of CONVERT:
|
1 2 3 |
SELECT CONVERT(VARCHAR(20), GETDATE(), 103); |
This SQL code will return the current date in the ‘dd/mm/yyyy’ format. Here, the style ‘103’ is used to get the date in this specific format.
Conclusion
Understanding CAST and CONVERT functions in SQL helps improve the versatility of your SQL querying skills. By casting and converting data, you can ensure information is correctly formatted for your specific needs, thereby improving the analysis and insights gained from the data.