New Parse Conversion Function in SQL Server 2012

SQL Server provides many conversion functions for converting one data type to another. The cast and convert functions provide similar functionality. They convert a value from one data type to another. Here, you will see the cast and convert functions similar to Parse and in which situation you cannot use the convert and cast functions. So let's have a look at a practical example of how to use a conversion parse function in SQL Server. The example is developed in SQL Server using the SQL Server Management Studio. 

Cast() Function

The Cast() function converts a data type variable or data from one data type to another data type. The Cast() function provides a data type to a dynamic parameter (?) or a NULL value.

Syntax

CAST ( [Expression]
AS Datatype)

The data type to which you are casting an expression is the target type. The data type of the expression from which you are casting is the source type.

Example

DECLARE @A varchar(2)

DECLARE @B varchar(2)

DECLARE @C varchar(2)

set @A=25

set @B=15

set @C=33

Select CAST(@A as int) + CAST(@B as int) +CAST (@C as int) as Result

Output

Convert() Function

When you convert expressions from one type to another, in many cases there will be a need within a Stored Procedure or other routine to convert data from a datetime type to a varchar type. The Convert function is used for such things. The CONVERT() function can be used to display date/time data in various formats.

Syntax

CONVERT(data_type(length), expression, style)

Style: style values for datetime or smalldatetime conversion to character data.  Add 100 to a style value to get a four-place year that includes the century (yyyy).

Example

In this example we use a style value 108 that defines the following format:

hh:mm:ss

Now use the style above in the following query:

select convert(varchar(20),GETDATE(),108)

Output

Parse Conversion Function

This function converts 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. The culture can be any of the .NET supported cultures, not 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 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 use CAST and CONVERT will fail that as in the following:

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 commands above. The output will be as below: