New Data Type Conversion Functions in SQL Server 2012

Today, I have provided an article showing how to utilize the new data type conversion functions in SQL Server 2012. These are the three functions:

1. Parse conversion Function
2. Try_Parse conversion Function
3. Try_Convert conversion Function

Let's take a look at a practical example. The example is developed in SQL Server 2012.

Parse conversion Function

This function is used to convert a string to Numeric and Date and Time formats. It will raise an error if translation isn't possible. You may still use CAST or CONVERT for general conversions. It depends on the presence of the CLR.

Syntax

To demonstrate this new conversion function the following defines the syntax:

 PARSE ( string_value AS data_type [ USING culture ] )

The Parse Function contains three parameters. The Culture part of the function is optional.

string_value - String value to parse into the Numeric and Date and Time format.

data_type - Returns data type, numeric or datetime type.

culture - Culture part of the function is optional. A language (English, Japanese, Spanish, Danish, French etc.) to be used by SQL Server to interpret data. A culture can be specified if needed; otherwise, the culture of the current session is used. Culture can be any of the .NET supported cultures, not being limited to those supported by SQL Server. 

For Example

In this example we see the parse function with Cast and Convert functions. Execute the following to convert a string value to datetime using CAST, CONVERT and PARSE functions:

SELECT CAST('6/08/2012' AS DATETIME2) AS  [CAST Function Result]  -- Using CAST Function

GO

SELECT CONVERT(DATETIME2, '06/08/2012') AS [CONVERT Function Result] --Using Convert Function

Go

SELECT PARSE('06/08/2012' AS Datetime2 USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

GO

Now press F5 to execute those commands. The result of the command produces are:

As you will see, only PARSE is able to convert the string value to datetime and the first two queries that are using CAST and CONVERT will fail that as shown below:

SELECT CAST('Monday, 06 august 2012' AS DATETIME2) AS  [CAST Function Result]  -- Using CAST Function

GO

SELECT CONVERT(DATETIME2, 'Monday, 06 august 2012') AS [CONVERT Function Result] --Using Convert Function

Go

SELECT PARSE('Monday, 06 august 2012' AS Datetime2  USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

GO

Now press F5 to execute the above commands. The output will be as below:

Try_Parse conversion Function

This function works similarly to the parse function except if the conversion is successful then it will return the value as the specified data type. Otherwise it will return a NULL value. 

Syntax

TRY_PARSE ( string_value AS data_type [ USING culture ] )

Example

Using Parse Function

SELECT Parse ('Sunday, 06 august 2012' AS Datetime2  USING 'en-US') AS [PARSE Function Result] -- Using Parse Function

OUTPUT

Now Using Try_Parse Function

SELECT Try_Parse ('Sunday, 06 august 2012' AS Datetime2  USING 'en-US') AS [Try_PARSE Function Result] -- Using Try_Parse Function
GO

OUTPUT

Try_Convert Function

This is similar to the convert function except it returns null when the conversion fails. If the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type, an error will be thrown.

Syntax

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

Example

SELECT TRY_CONVERT(Datetime2, '06/08/2012') AS [Try_Convert Function Result] -- Using Try_Convert Function

In the above example the conversion cannot be completed because the data type of the expression is not allowed to be explicitly converted to the specified data type.

Conclusion

Use PARSE and TRY_PARSE when you need culture-aware conversions of string to numeric or date/time data types. TRY_PARSE and TRY_CONVERT are used to get null back when the conversion fails.