SQL server 2012 – PARSE() & TRY_PARSE()

In SQL Server 2012 you have a lot new features to use and some of the improvements are in T-SQL. If you ever tried to convert a string value into a date or numeric datatype, you probably have been facing incompatible string values that are not possible to convert without some easy or complex string manipulations. I guess you have been facing issues like this: SELECT CONVERT(datetime,’Monday, 13 December 2010′) AS Result SELECT CAST(‘måndag 13 december 2010 kl 10:12′ AS datetime2) AS Result SELECT CONVERT(money,’3 455,98′) AS Result SELECT CONVERT(datetime2, ’12/16/2010’) All of these statements will fail and you have to manipulate the strings to be able to convert them. Sometimes these issues will arise in implicit conversions when you try to insert string values into date or numeric columns and you need to find the record that is causing the problem. These issues can be solved by two of the new functions in SQL server 2012, PARSE() & TRY_PARSE().  

With these functions you can use a language culture to handle different string formats. SELECT PARSE(‘Monday, 13 December 2010’ AS datetime2 using ‘en-US’) AS Result SELECT PARSE(‘Måndag 13 December 2010’ AS datetime2 using ‘sv-SE’) AS Result SELECT PARSE(‘Måndag den 13 December 2010’ AS datetime2 using ‘sv-SE’) AS Result SELECT PARSE(‘måndag 13 december 2010 kl 10:12’ AS datetime2 using ‘sv-SE’) AS Result SELECT PARSE(‘måndag 13 december 2010 klockan 10:12’ AS datetime2 using ‘sv-SE’) AS Result — Error! All of the selects above will return a valid datetime2 except the last on results in an error. To avoid the error you can replace the PARSE function with TRY_PARSE that will give you a NULL value instead of an error.

SELECT PARSE(‘€345,98’ AS money using ‘de-DE’) AS Result SELECT PARSE(‘£345,98’ AS money using ‘de-DE’) AS Result — Error! SELECT TRY_PARSE(‘£345,98’ AS money using ‘de-DE’) AS Result SELECT TRY_PARSE(‘€345,98’ AS money using ‘de-DE’) AS Result In the example above you’ll get an error on the second statement, becuase the £ sign is not a valid currency prefix for German language culture. The most interesting result you’ll get for the following code: SELECT PARSE(‘1 53’ AS DECIMAL(10,2)) AS Result SELECT PARSE(‘1,53’ AS DECIMAL(10,2)) AS Result SELECT TRY_PARSE(‘1,53’ AS DECIMAL(10,2) USING ‘sv-SE’) AS Result SELECT PARSE(‘1 53’ AS DECIMAL(10,2) USING ‘sv-SE’) AS

Result As you can see in the result is that the Thousand sign for the specified language culture is just removed, causing 1,53 and 1 53 to become 153, instead of causing an exception. The thousand sign doesn’t seem to have a meaning in PARSE, and this might cause you some sleepless nights if you thought you would get a different result.

If you have any questions regarding SQL server, feel free to contact us and our SQL server experts will help you.