PARSE() Conversion Function

Introduction 

We all know that to convert data type (form one data type to another), we must use the CONVERT or CAST data conversion function. Microsoft SQL Server 2012 gives us some new data type conversion function that we have to understand.  

Here in this article we are trying to discuss about PARSE()  data conversion function came from Microsoft SQL Sever 2012 onwards. Hope all of you find it informative.

PARSE()

PARSE() function convert any string values to Numeric or Date/Time format. If PARSE() cannot convert, it will returns a zero. PARSE() will result to an error. PARSE() function relies on Common Language Runtime (CLR) to convert the string value. If there is no CLR installed on the server, PARSE() function will return an error. 

Syntax: 

PARSE ( String_Value AS data_type   [ USING culture ] )

Parameter Details:

String_Value	String expression which needs to be parsed.
data_type	Output data type, e.g. INT, NUMERIC, DATETIME etc.
Culture	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

-- PARSE String to INT

SELECT PARSE('1000' AS INT) AS 'String to INT'

GO

String to INT

-------------

1000

-- PARSE String to Numeric

SELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'

GO

String to Numeric

---------------------------------------

1000.06

-- PARSE String to DateTime

SELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'

GO

String to DATETIME

-----------------------

2013-05-18 00:00:00.000

-- PARSE String to DateTime

SELECT PARSE('2013/05/18' as DATETIME) AS 'String to DATETIME'

GO

String to DATETIME

-----------------------

2013-05-18 00:00:00.000

-- PARSE string value in the India date format to DateTime

SELECT PARSE('18-05-2013' as DATETIME using 'en-in') AS 'Indian DateTime Format'

GO

Indian DateTime Format

-----------------------

2013-05-18 00:00:00.000

-- PARSE string value is in the US currency format to Money

SELECT PARSE('$2500' as MONEY using 'en-US') AS 'US Currency Format to MONEY'

GO

US Currency Format to MONEY

---------------------------

2500.00

-- Best Conversion by PARSE

SELECT PARSE('08-JUNE-2013' AS DATETIME)

GO

SELECT PARSE('08-JUN-2013' AS DATETIME)

GO

SELECT PARSE('2013JUNE08' AS DATETIME)

GO

SELECT PARSE('08/JUN/2013' AS DATETIME)

GO

Output is

-----------------------

2013-06-08 00:00:00.000

Difference Between PARSE() and CONVERT()

Here we are providing an example where PARSE() is converting the Value to Date/Time but CONVERT() fails to do.

SELECT PARSE('Saturday, 08 June 2013' AS DATETIME) AS 'PARSE Result'

GO

PARSE Result

-----------------------

2013-06-08 00:00:00.000

SELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') AS 'CONVERT Result'

GO

CONVERT Result

-----------------------

Msg 241, Level 16, State 1, Line 1

Conversion failed when converting date and/or time from character string.

In next several article we are trying to discuss about others datra Conversion Function of Microsoft SQL Server 2012.

Hope you like it.

Posted by: JOYDEEP DAS