{"id":943,"date":"2013-06-08T19:21:32","date_gmt":"2013-06-08T13:51:32","guid":{"rendered":"http:\/\/sqlhints.com\/?p=943"},"modified":"2013-07-07T03:06:08","modified_gmt":"2013-07-06T21:36:08","slug":"try_convert-sql-server-2012-built-in-conversion-function","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/06\/08\/try_convert-sql-server-2012-built-in-conversion-function\/","title":{"rendered":"TRY_CONVERT CONVERSION FUNCTION IN SQL SERVER 2012"},"content":{"rendered":"

TRY_CONVERT <\/strong>is one of the new built-in conversion function introduced as a Part of Sql Server 2012.\u00a0<\/strong>TRY_CONVERT function is similar to the CONVERT \u00a0function, but if\u00a0CONVERT\u00a0function fails to convert the value to the requested type then throws an exception where as TRY_CONVERT function returns NULL value.<\/p>\n

Note: \u00a0TRY_CONVERT function raises an exception if we try to an convert expression to a type which is not explicitly permitted.<\/p>\n

Syntax: \u00a0TRY_CONVERT (data_type [ ( length ) ], expression [, style ])<\/p>\n

Parameter Details:<\/strong><\/p>\n

data_type<\/strong>\u00a0 \u00a0 \u00a0: The data type into which to cast expression.
\nexpression<\/strong>\u00a0 : The value to be cast.
\nstyle<\/strong>\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0: Optional integer value that specifies how the TRY_CONVERT function is to translate expression. The Values for this integer parameter are same as the one used by CONVERT function.<\/p>\n

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

TRY_CONVERT – Succeeds<\/strong><\/span><\/p>\n

SELECT TRY_CONVERT(INT, '100')\r\nSELECT TRY_CONVERT(NUMERIC(8,2), '1000.06')\r\nSELECT TRY_CONVERT(INT, 100)\r\nSELECT TRY_CONVERT(NUMERIC(8,2), 1000.06)\r\nSELECT TRY_CONVERT(DATETIME, '05\/18\/2013')\r\nSELECT TRY_CONVERT(DATETIME, '05\/18\/2013',111)<\/pre>\n
RESULT:<\/strong><\/p>\n
\"TRYCONVERTSUCCEEDS\"<\/a><\/p>\n
TRY_CONVERT – Fails<\/strong><\/span><\/p>\n
In the below example we are trying to convert an invalid date i.e. 31st Feburary, 2013. In such cases TRY_CONVERT function will return NULL value instead of throwing exception.<\/span><\/p>\n
SELECT TRY_CONVERT(DATETIME, '02\/31\/2013') \r\n            AS 'TRY_CONVERT Function Fails'<\/pre>\n
Result:
\nTRY_CONVERT Function Fails
\n————————–
\nNULL<\/p>\n
TRY_CONVERT – Throws Exception<\/strong><\/span><\/p>\n
In the below example we are trying to convert an integer value to XML type which is not permitted explicitly, in such cases TRY_CONVERT function raises an exception.<\/p>\n
SELECT TRY_CONVERT(XML, 10)<\/span><\/p>\n
Result:
\nMsg 529, Level 16, State 2, Line 1
\nExplicit conversion from data type int to xml is not allowed.<\/span><\/p>\n
But on the other-hand conversion of an string value to an XML type is supported. Let us see this with an example:<\/p>\n
SELECT TRY_CONVERT(XML, '10') AS 'XML Output'<\/pre>\n
Result:<\/p>\n
XML Output
\n——————————-
\n10<\/p>\n
Difference Between CONVERT and TRY_CONVERT<\/strong><\/span><\/p>\n
Both CONVERT and TRY_CONVERT function converts the expression to the requested type. But if the CONVERT function fails to convert the value to the requested type then raises an exception, on the other hand if TRY_CONVERT function returns a NULL value if it fails to convert the value to the requested type. Below example demonstrates this difference<\/p>\n
SELECT CONVERT(DATETIME, '02\/31\/2013')\r\n            AS 'CONVRT Function Result'\r\nSELECT TRY_CONVERT(DATETIME, '02\/31\/2013') \r\n            AS 'TRY_CONVERT Function Result'<\/pre>\n
Result:
\nCONVRT Function Result
\n———————–
\nMsg 242, Level 16, State 3, Line 1
\nThe conversion of a varchar data type to a datetime data type resulted in an out-of-range value.<\/span><\/p>\n
TRY_CONVERT Function Result
\n————————-
\nNULL<\/p>\n
Below example demonstrate how we can check the result of TRY_CONVERT function in IF condition:<\/strong><\/span><\/p>\n
IF TRY_CONVERT(DATETIME,'Basavaraj') IS NULL\r\n        PRINT 'TRY_CONVERT: Conversion Successful'\r\nELSE\r\n        PRINT 'TRY_CONVERT: Conversion Unsuccessful'<\/pre>\n
Result:<\/strong>
\nTRY_CONVERT: 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_CONVERT is one of the new built-in conversion function introduced as a Part of Sql Server 2012.\u00a0TRY_CONVERT function is similar to the CONVERT \u00a0function, but if\u00a0CONVERT\u00a0function fails to convert the value to the requested type then throws an exception where as TRY_CONVERT function returns NULL value. Note: \u00a0TRY_CONVERT function raises an exception if we try … Continue reading TRY_CONVERT 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,91,93,35,986,55,90,92],"class_list":["post-943","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-convert-vs-try_convert","tag-difference-between-convert-and-try_convert","tag-new-feature-in-sql-server-2012","tag-sql-server","tag-sql-server-2012","tag-try_convert","tag-try_convert-vs-convert"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-fd","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/943","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=943"}],"version-history":[{"count":31,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/943\/revisions"}],"predecessor-version":[{"id":966,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/943\/revisions\/966"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=943"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=943"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=943"}],"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