Difference between CAST, CONVERT, and PARSE SQL Server? Example Tutorial

Though all three, CAST, CONVERT, and PARSE are used to convert one data type into another in SQL Server, there are some subtle differences between them. The  CAST method accepts just two parameters, expression, and target type, but CONVERT() also takes a third parameter representing the format of conversion, which is supported for some conversions, like between character strings and date-time values. For example, CONVERT(DATE, '2/7/2015', 101) converts the character string '2/7/2015' to DATE using DATE format 101, representing United States standard.

By using the PARSE function, you can also indicate the culture by using any culture supported by Microsoft's dot NET framework. For example, PARSE('7/8/2015' AS DATE USING 'en-US') parses the input literal as a DATE by using the United States English Culture, similar to 101 formatting style.

By the way, if you are new to Microsoft SQL Server and T-SQL then I also suggest you join a comprehensive course to learn SQL Server fundamentals and how to work with T-SQL. If you need a recommendation then I suggest you go through the Microsoft SQL for Beginners online course by Brewster Knowlton on Udemy. It's a great course to start with T-SQL and SQL queries in SQL Server.

Difference between CAST vs CONVERT vs PARSE in SQL Server

Here are some other differences between CAST, CONVERT, and PARSE methods for data type conversion in SQL Server:

1. ANSI SQL Standard

CAST is supported by ANSI SQL Standard, so it's a best practice to prefer CAST over CONVERT and PARSE if it's enough to do the job.

2. .NET and CLR Dependency

PARSE function relies on the presence of the .NET Framework common language runtime (CLR), which may be an extra dependency and may not be present in every Windows server where you have installed Microsoft SQL Server.

3. Optional USING Clause

The PARSE function supports an optional USING clause indicating the culture, which is any valid culture supported by the .NET framework. If culture is not specified then it will use the current session's effective language.

4. Syntax

Using CAST:
CAST ( expression AS data_type )

Using CONVERT:
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

Using PARSE
PARSE ( string_value AS data_type [ USING culture ] )  


Both CAST and CONVERT are used to explicitly converts an expression of different data types in SQL

5. Examples

Let's see some examples to convert DATE to VARCHAR in Microsoft SQL Server using the cast(), convert(), and parse function.

3. CAST Function Example

Let's some examples of CAST function to convert the DATETIME data type to VARCHAR and  VARCHAR data type to SMALLINT data type in SQL Server:

-- casting DATE to VARCHAR in SQL Server
SELECT CAST(GETDATE() AS VARCHAR(30)) AS Today

Today
Apr 25 2017 6:32AM


-- CASTING VARCHAR to INT in Microsoft SQL Server

SELECT CAST('1234' AS SMALLINT) AS Number
Number
1234

You can see that the casting has been successful. If you want to learn more about the CAST function and how to convert all SQL Server data types e.g. numeric, money, datetime2 into VARCHAR, and others, I suggest you read joining these free SQL Server and T-SQL courses or reading Querying Microsoft SQL Server, one of the best books I have read in SQL Server so far. 

It is actually a study guide of Microsoft SQL Server certification Exam 70-461 but at the same time one of the greatest books to learn T-SQL fundamentals as well.

2. CONVERT Function Example

Now, let's try to convert the same values using the Convert function in SQL Server:

-- converting DATE to VARCHAR in SQL Server 
SELECT CONVERT(VARCHAR(20), GETDATE(), 101) AS Today
Today
07/23/2015

-- converting VARCHAR to INT in Microsoft SQL Server
SELECT Convert(bigint, '222222') AS MagicNumber
MagicNumber
222222

Convert function is mainly used to convert Date to VARCHAR value into different date formats as shown here.

Here is the screenshot of executing SQL queries with CAST and Convert in SQL Server Management Studio:

3. PARSE Function Example

Let's see some examples of PARSE function to convert VARCHAR data type to DATETIME2 and MONEY data type using different locale or cultures:

-- Parsing VARCHAR to DATETIME2 data type
SELECT PARSE('Monday, 25 December 2017' AS datetime2 
       USING 'en-US') AS CurrentDate; 

CurrentDate
2017-12-25 00:00:00.0000000

-- Parsing VARCHAR with currency symbol to MONEY data type
SELECT PARSE('€345,98' AS money USING 'de-DE') AS Price; 

Price
345.98

You can see that the amount of currency value Euro is parsed correctly because of German culture, but if you try to change the currency symbol to $ it will not parse and give you an error as shown below:

-- Parsing VARCHAR with dollar currency symbol to MONEY data type 
--- using german culture
SELECT PARSE('$345,98' AS money USING 'de-DE') AS Price; 

Msg 9819, Level 16, State 1, Line 2
Error converting string value '$345,98' into data type money 
using culture 'de-DE'.

But as soon as you change the culture to en-US it will be able to parse the currency value correctly, but do note down the actual value which is very different from the german one, that's where culture can make a big difference. 

-- Parsing VARCHAR with dollar currency symbol to MONEY data type 
--- using US culture
SELECT PARSE('$345,98' AS money USING 'en-US') AS Price; 

Price
34598.00

Here is one more example of using the PARSE function in SQL Server to parse String using implicit language setting

-- PARSE with implicit setting of language
-- The English language is mapped to en-US specific culture  
SET LANGUAGE 'English';  
SELECT PARSE('04/16/2017' AS datetime2) AS Output;  

Output
2017-04-16 00:00:00.0000000

Here is the screenshot of executing PARSE related SQL queries on the SSMS tool:

That's all about the difference between CAST, CONVERT, and PARSE in SQL SERVER. Prefer CAST over CONVERT and PARSE because it's ANSI standard and your query will be more portable across different database vendors. I generally prefer CAST for casting between VARCHAR and NUMERIC type, but I prefer to use CONVERT for converting String literals into DATE, TIME, and DATETIME types. I don't use PARSE, but it is something good to know about it.

Other Microsoft SQL Server tutorials and articles you may like

• Difference between rank(), row_number(), and dense_rank() in SQL? (answer)
• How to replace NULL with empty String in SQL Server? (tutorial)
• Difference between coalesce() and isNull() in Microsoft SQL Server? (answer)
• How to remove duplicate rows from a table in SQL? (solution)
• How to split String in SQL Server? (answer)
• How to convert the result of a SELECT command into a CSV String? (example)
• 5 Web sites to learn SQL online for FREE? (resource)
• How to find the length of a string in SQL Server? (example)
• How to find all customers who have never ordered? (solution)
• The right way to check for NULL values in SQL query? (example)
• What is the difference between closing and deallocating a cursor? (answer)
• How to create an Identity column in SQL Server? (example)
• The right way to compare dates in SQL query? (example)
• How to add columns into an existing table in MSSQL? (example)

P. S.- If you are working in Microsoft SQL Server but doesn't feel very confident when using SQL Server and T-SQL specific features and functions then I suggest you join one of these best online course on Microsoft SQL Server. This is an excellent resource to start learning the MSSQL databases from scratch.