New Parse Function in SQL Server 2012

In this article I have described new parse function in SQL Server 2012.
  • 4309

Parse 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:

New-SQL-Function7.jpg

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:

New-SQL-Function8.jpg

Categories

More Articles

© 2013 dotNetheaven. All rights reserved.