Search

Monday, September 5, 2011

SQL Server Denali– PARSE() and TRY_PARSE() conversion functions


PARSE() function converts expression (string value) to date/time and number data types if conversion is possible. If conversion isn’t possible it raises an error.

TRY_PARSE() function converts expression (string value) to date/time and number data types or return NULL if conversion isn’t possible. It basically identifies if the type specified is applicable for parsing or not and returns the appropriate status. 

We often see the error, "Conversion failed when converting the varchar value 'dsfds' to data type int.". Now we can avoid this error by using Try_Parse() function. If we are in doubt that there might be some invalid values, by using this TRY_PARSE() function, we can avoid this errors and it will generate NULL values for invalid values. You can convert data to different datatype using new try_parse() function introduced in SQL Server Denali. It returns data if parse is successful otherwise it returns null.

Example

SELECT PARSE('08' AS datetime)
-- Raised an error
--Msg 9819, Level 16, State 1, Line 1
--Error converting string value '15' into data type datetime using culture ''.
SELECT TRY_PARSE('08' AS datetime2)
--  Returned NULL
SELECT PARSE('8' AS NUMERIC(15,2))
-- Returned 8.00
SELECT TRY_PARSE('8' AS NUMERIC(15,2))
-- Returned 8.00
GO


No comments:

Post a Comment