SQL CAST And CONVERT Function

Introduction

If you're one of those confused about the difference between the CAST and CONVERT functions, you have come to the right place.

That's why in this article, we'll explore the CAST and CONVERT functions of SQL Server and see their difference.

OK, let's get started then.

What is the SQL CAST function?

Basically, this function converts data from one data type to another. For example, it can convert numeric data into character or string data.

Things to Remember about SQL Cast Function

Transforms the expression data from one data type to another.
The transformation lasts only for the life of the query.
It is an ANSI standard (ANSI SQL-92 compliant), portable to other DBMS.
So if you're comfortable with the CAST function, you'll be able to use it on other SQL-related databases.
If in case the conversion fails, it will return an error. Otherwise, it returns the converted value.
Doesn't have a parameter that accepts optional style for formatting. That's why you can use the SQL FORMAT function.

Syntax

CAST([Expression] as DataType (length))

[Expression] – defines the valid expression argument.
DataType – the target data type for conversion.
Length (optional) – the specified length of the target data type.

Examples

Example 1

Let's try to convert a string to DateTime data type.

--Let's declare a type
DECLARE  @YOUR_BIRTHDAY NVARCHAR(10) = '01/29/2000';

--SYNTAX: CAST([Expression] AS DataType)
SELECT @YOUR_BIRTHDAY AS [Data To Convert], 
CAST(@YOUR_BIRTHDAY AS DATETIME) AS [BirthDate - Data Converted];

Output

sql_cast_function

Example 2

Let's add two or more random numbers and convert them into money data type.

--Let's declare two numbers 
DECLARE @NUM1 INT = 100, @NUM2 BIGINT = 854775808

--Let's add them together and convert to money and format a bit so it would look like a genuine $. :-)
SELECT FORMAT(CAST ((@NUM1 + @NUM2) AS MONEY), '$ #,##0.00') [Sum];

cast_function_sql

Example 3

Let's convert two random char data type to int data type and multiply it together.

--Let's declare 2 char with random number assigned to it 
DECLARE @char1 CHAR(3) = '100', @char2 CHAR(3) = '569';

--Let's multiply the two char data-type and see the product of those two numbers.
SELECT CAST(@char1 AS INT) * CAST(@char2  AS INT) [Product of Two Numbers]

Output

cast_sql_function

What is SQL CONVERT function?

This function converts an expression from one data type to another, just like the CAST function. Another good thing about the SQL CONVERT function is that it gives you the option to format your converted data. That's why we can say that it is more powerful compared to the CAST function.

Things to Remember about SQL Convert Function

A non-ANSI SQL-92 compliant function converts the expression data type to another data type with a formatting option.
This function is specific to Microsoft's TSQL and will not be portable to other DBMS.

Syntax

CONVERT (DataType, (length), expression, style)

DataType – target data type to which you want to convert.
Length – the specified length of the target type.
Expression – the target data type for conversion.
Style – optional integer value to specify the style format of the output. Cool right?

Example

Let's create a temp table, insert the data of this year's Chinese New Year, convert it into VARCHAR, and pass some formatting options.

DROP  TABLE IF EXISTS temp.dbo.#CONVERT_FUNCTION_STYLES

CREATE TABLE #CONVERT_FUNCTION_STYLES
(Id int, 
 [Name] varchar(10), 
 [ConvertedDate] varchar(20)) 

 DECLARE @CHINISE_NEWYEAR DATETIME = '02/01/2022 12:00:00 AM';

 INSERT INTO #CONVERT_FUNCTION_STYLES 
 (Id, Name, [ConvertedDate])
 VALUES
 (1, 'Default', CONVERT(VARCHAR,@CHINISE_NEWYEAR, 100)),
 (2, 'US',      CONVERT(VARCHAR, @CHINISE_NEWYEAR, 101)),
 (3, 'ANSI',    CONVERT(VARCHAR, @CHINISE_NEWYEAR, 102)),
 (4, 'German',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 104)),
 (4, 'Japan',  CONVERT(VARCHAR, @CHINISE_NEWYEAR, 111));

 SELECT @CHINISE_NEWYEAR [Date and Time To Be Converted],
	   Name [Country],
	   ConvertedDate [Country Standard Converted Date]  
	   
 FROM #CONVERT_FUNCTION_STYLES;

Output

sql_convert_function

Summary

This article has discussed the CAST and CONVERT functions, including their syntax and things to remember about these functions.

Stay tuned for more. Until next time, happy programming!

Please don't forget to bookmark, like, and comment. Cheers, and Thank you!