{"id":993,"date":"2013-06-08T22:43:56","date_gmt":"2013-06-08T17:13:56","guid":{"rendered":"http:\/\/sqlhints.com\/?p=993"},"modified":"2013-07-07T03:08:09","modified_gmt":"2013-07-06T21:38:09","slug":"try_parse-sql-server-2012-built-in-conversion-function","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/06\/08\/try_parse-sql-server-2012-built-in-conversion-function\/","title":{"rendered":"TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012"},"content":{"rendered":"

TRY_PARSE <\/strong>is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_PARSE function is Similar to PARSE function, but if PARSE function fails to convert the value throws an exception where as TRY_PARSE function returns a NULL value.<\/p>\n

Important Note:<\/strong>\u00a0TRY_PARSE function is not a native SQL SERVER function, instead it is a .NET Framework Common Language Run-time dependent function. Then obviously it will have the performance overhead and also requires the presence of .NET CLR on the database Server. Continue to use the existing CAST and CONVERT functions wherever it is possible.<\/p>\n

Syntax: \u00a0TRY_PARSE ( string_value AS data_type [ USING culture ] )
\n
\nParameter Details:<\/strong>
\nstring_value\u00a0<\/strong>: String expression which needs to be parsed.
\ndata_type <\/strong>: Output data type, e.g. INT, NUMERIC, DATETIME etc.
\nculture <\/strong>: 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.<\/p>\n

Now let us understand this TRY_PARSE function with examples:<\/strong><\/p>\n

-- TRY_PARSE String to INT\r\nSELECT TRY_PARSE('1000' AS INT) AS 'String to INT'\r\n-- TRY_PARSE String to NUMERIC\r\nSELECT TRY_PARSE('1000.06' AS NUMERIC(8,2)) \r\n           AS 'String to NUMERIC'\r\n-- TRY_PARSE String to DATETIME\r\nSELECT TRY_PARSE('05-18-2013' AS DATETIME) \r\n           AS 'String to DATETIME' \r\n-- TRY_PARSE String to DateTime\r\nSELECT TRY_PARSE('2013\/05\/18' AS DATETIME) \r\n           AS 'String to DATETIME'\r\n-- TRY_PARSE string value in the India date format to DATETIME\r\nSELECT TRY_PARSE('18-05-2013' AS DATETIME using 'en-in') \r\n AS 'String in the India date format to DATETIME'\r\n-- TRY_PARSE string value is in the US currency format to Money \r\nSELECT TRY_PARSE('$2500' AS MONEY using 'en-US')\r\n AS 'String in the US currency format to MONEY'<\/pre>\n
Result:<\/strong>
\n\"TRY_PARSE_CONVERSION_FUNCTION_IN_SQL_SERVER_2012\"<\/a><\/p>\n
Difference between PARSE and TRY_PARSE<\/strong><\/p>\n
Try to convert invalid value, in this case PARSE throws exception but TRY_PARSE returns NULL Value<\/p>\n
SELECT PARSE('Basavaraj' as DATETIME) 'PARSE RESULT'\r\nGO\r\nSELECT TRY_PARSE('Basavaraj' as DATETIME) 'TRY_PARSE RESULT'<\/pre>\n
Result:<\/p>\n
<\/strong><\/p>\n
PARSE RESULT
\n———————–
\nMsg 9819, Level 16, State 1, Line 1
\nError converting string value ‘Basavaraj’ into data type datetime using culture ”.<\/span><\/p>\n
TRY_PARSE RESULT
\n———————–
\nNULL<\/p>\n
Below example demonstrate how we can check the result of TRY_PARSE function in IF condition:<\/strong><\/p>\n
IF\u00a0\u00a0TRY_PARSE('Basavaraj'\u00a0as DATETIME) IS NULL\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0PRINT 'TRY_PARSE: Conversion Successful'\r\nELSE\r\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0PRINT 'TRY_PARSE: Conversion Unsuccessful'<\/pre>\n
Result:<\/strong>
\nTRY_PARSE: Conversion Successful<\/p>\n
You may like to read the below new built-in function’s introduced in Sql Server 2012:<\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n<\/table>\n","protected":false},"excerpt":{"rendered":"
TRY_PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. TRY_PARSE function is Similar to PARSE function, but if PARSE function fails to convert the value throws an exception where as TRY_PARSE function returns a NULL value. Important Note:\u00a0TRY_PARSE function is not a native SQL SERVER function, instead … Continue reading TRY_PARSE CONVERSION FUNCTION IN SQL SERVER 2012<\/span> →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[89,13,3,5],"tags":[989,100,35,101,986,55,99,102],"class_list":["post-993","post","type-post","status-publish","format-standard","hentry","category-conversion-functions","category-functions","category-sql-server","category-sql-server-2012-sql-server","tag-conversion-functions","tag-difference-between-parse-and-try_parse","tag-new-feature-in-sql-server-2012","tag-parse-vs-try_parse","tag-sql-server","tag-sql-server-2012","tag-try_parse","tag-try_parse-vs-parse"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-g1","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/993","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/comments?post=993"}],"version-history":[{"count":21,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/993\/revisions"}],"predecessor-version":[{"id":1509,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/993\/revisions\/1509"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=993"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=993"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=993"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
New Built in Functions introduced in Sql Server<\/strong><\/td>\n
CONVERSION FUNCTIONS<\/strong><\/td>\n
PARSE<\/a><\/strong><\/td>\nTRY_PARSE<\/a> <\/strong><\/td>\n
TRY_CONVERT<\/a><\/strong><\/td>\n
STRING FUNCTIONS<\/strong><\/td>\n
CONCAT<\/a><\/strong><\/td>\nFORMAT<\/a><\/strong<\/td>\n
LOGICAL FUNCTIONS<\/strong><\/td>\n
CHOOSE<\/a><\/strong><\/td>\nIIF<\/a><\/strong<\/td>\n
DATE AND TIME FUNCTIONS<\/strong><\/td>\n
EOMONTH<\/a><\/strong><\/td>\n
DATEFROMPARTS<\/a><\/strong><\/td>\nDATETIMEFROMPARTS<\/a><\/strong<\/td>\n
SMALLDATETIMEFROMPARTS<\/a> <\/strong><\/td>\nDATETIME2FROMPARTS <\/strong<\/td>\n
TIMEFROMPARTS <\/strong><\/td>\nDATETIMEOFFSETFROMPARTS<\/strong<\/td>\n