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
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];
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
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
HostForLIFEASP.NET SQL Server 2019 Hosting
