PARSE And TRY_PARSE In SQL Server DENALI (MSSQL2011)

PARSE : A new Function Added In SQL Server Denali (SQL Server 2011). Its very useful function and converts string values to Requested Data Type. Requested Data Type has Limiting DataType Involved. I am really Excited to See DataTime From String Value. Its Really Helpful.

Now Try To Answer Following Question :

• Convert Following String Values Into DateTime
  • SEPTEMBER 16 2011
  • SEP 16 2011
  • sep/16/2011
  • sep.16.2011
  • 9-16-2011
  • 9 16 2011
  • 09 2011
  • Sep11

So Converting Above String Into Datatime will take Considerable time and requires lots of engineering in T-SQL to Achieve It.

But Believe it or not Its really easy with PARSE() function introduced in SQL Server Denali(SQL Server 2011).

See Following

select 'SEPTEMBER 16 2011' as StringValue, Parse('SEPTEMBER 16 2011' as datetime) as Parsed_Value
Union All
select 'SEP 16 2011' as StringValue,Parse('SEP 16 2011' as dateTime) as Value
Union All
select 'sep/16/2011' as StringValue,Parse('sep/16/2011' as dateTime) as Value
Union All
select 'sep.16.2011' as StringValue,Parse('sep.16.2011' as dateTime) as Value
Union All
select '9-16-2011' as StringValue,Parse('9-16-2011' as dateTime) as Value
Union All
select '9 16 2011' as StringValue,Parse('9 16 2011' as dateTime) as Value
Union All
select 'Sep11' as StringValue,Parse('Sep11' as dateTime) as Value
Go

Output:

I have seen converting string values into datetime was never this much easy.

PARSE Function Can Parse String Value bases on Culture. Suppose you have DateTime Formatted in Different Culture. then with Optional Parameter in PARSE function you can define culture of string value so that PARSE can recognize the string value and convert it to desired data type.

See Following String Value

शनिवार, अक्तूबर-01-2011

Now If We Have To Convert it To DateTime Using PARSE.

select Parse(N'शनिवार, अक्तूबर-01-2011' as datetime)

Output:

Msg 9819, Level 16, State 1, Line 1
Error converting string value 'शनिवार, अक्तूबर-01-2011' into data type datetime using culture ''.

This is because PARSE was unable to understand culture of string value passed Now Let Pass Culture of String Value which is hindi-indian [hi-in].

Now Run Following Query

select Parse(N'शनिवार, अक्तूबर-01-2011' as datetime Using 'hi-in')

Output;

What happens When PARSE() in Unable Parase String Value ?

Lets See

Select Parse('SEPT. 16 2011' as Datetime) As Value

Output:

Msg 9819, Level 16, State 1, Line 1
Error converting string value 'SEPT. 16 2011' into data type datetime using culture ''.

So to Handle These Exception you can use TRY_PARSE() function which returns NULL if String Value cannot be converted to Specified DataType.

Let’s Use TRY_PARSE() For Above Query

Select TRY_PARSE('SEPT. 16 2011' as Datetime) As Value

Output:

Value
-----------------------
NULL

(1 row(s) affected)