Tuesday, April 25, 2017

Difference between CAST, CONVERT, and PARSE function in Microsoft SQL Server

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 the Microsoft's  dot NET framework. For example, PARSE('7/8/2015' AS DATE USING 'en-US') parse the input literal as a DATE by using a United State English Culture, similar to 101 formatting style.


CAST vs CONVERT vs PARSE in MSSQL

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

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

2) 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) 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 example to convert DATE to VARCHAR in Microsoft SQL Server using the cast(), convert(), and parse function.

CAST Function Example
Let's some example of CAST function to convert DATATIME 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 CAST function and how to convert all SQL Server data types e.g. numeric, money, datetime2 into VARCHAR and others, I suggest you to reading Querying Microsoft SQL Server 2012, 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 book to learn T-SQL fundamental as well.

Difference between CAST, CONVERT, and PARSE function in Microsoft SQL Server



CONVERT Function Example
Now, let's try to convert same values using 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 format as shown here.

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

SQL Server CAST, Convert and PARSE Function example



PARSE Function Example
Let's see some example of PARSE function to convert VARCHAR data type to DATETIME2 and MONEY data type using different locale or culture:

-- 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 a 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 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 SSMS tool:

CAST vs CONVERT vs PARSE in SQL Server



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 is something good to know about it.

Further Learning
Introduction to SQL by Jon Flanders
SQL Server Fundamentals by Dan Sullivan
Microsoft SQL Server 2012 Internals

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 2008? (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 close and deallocate 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 200, 2012 or 2014 version but doesn't feel very confident when using SQL Server and T-SQL specific features and functions then I suggest you reading a good book on Microsoft SQL Server e.g. Microsoft SQL Server 2012 T-SQL Fundamentals (Developer Reference) 1st Edition by Itzik Ben-Gan.. This is an excellent book to start learning MSSQL database from scratch.

No comments :

Post a Comment