New Conversion Functions in SQL Server 2012

Conversion Functions

Parse Function
This function converts a string to Numeric, date and time formats. It will raise error if translation isn’t possible. That time 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 as follows

PARSE(string_value AS data_type[Using culture])

The above function contains 3 parameters.
• String_value: String value to parse into the Numeric, Date and Time format.
• Data_type: returns datatype, numeric or datetime type.
• Culture: it is optional string that identifies the culture in which string_value is formatted. If it is not specified, then it takes the language of the current session.

Example 1: Convert string to datetime

Select parse(‘06/08/2014’ AS DateTime2) as Datetimevalue

Output:

Example 2: Convert string to int

Select parse(‘100.000’ AS int) as intvalue

Output:


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

Syntax:

TRY_PARSE(string_value AS date_type[Using Culture])

Example: Using Try_Parse

Select Try_Parse(‘Sunday, 05 august 2012’ AS Datetime2 Using ‘en-US’) as [TryParseFunctionResult]

Output:


Try_Convert Function
This is similar to the covert function except it returns null when the conversion fails.

Syntax:

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

Example 1: Convertion failed and gives null value

SELECT
CASE WHEN TRY_CONVERT(float, 'test') IS NULL
THEN 'Cast failed'
ELSE 'Cast succeeded'
END AS Result;
GO

Output:

Example 2: The Expression must be in the Excepted format.

SET DATEFORMAT mdy;
SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result;
GO

Output: