{"id":968,"date":"2013-06-08T21:34:45","date_gmt":"2013-06-08T16:04:45","guid":{"rendered":"http:\/\/sqlhints.com\/?p=968"},"modified":"2013-07-07T03:07:18","modified_gmt":"2013-07-06T21:37:18","slug":"parse-sql-server-2012-built-in-conversion-function","status":"publish","type":"post","link":"https:\/\/sqlhints.com\/2013\/06\/08\/parse-sql-server-2012-built-in-conversion-function\/","title":{"rendered":"PARSE CONVERSION FUNCTION IN SQL SERVER 2012"},"content":{"rendered":"

PARSE <\/strong>is one of the new built-in conversion function introduced as a Part of Sql Server 2012. PARSE function converts the string expression to the requested data type. It tries it’s best to translate the string\u00a0value to requested type but if it fails to\u00a0translate then raises an exception.<\/p>\n

Important Note:<\/strong> This PARSE function is not a native Sql 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:\u00a0 PARSE ( string_value AS data_type [ USING culture ] )
\n
\nParameter Details:<\/strong>
\nstring_value\u00a0<\/strong>\u00a0\u00a0: String\u00a0expression which needs to be parsed.
\ndata_type<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: Output data type, e.g. INT, NUMERIC, DATETIME etc.
\nculture<\/strong>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0: 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\u00a0PARSE function with examples:<\/strong><\/p>\n

-- PARSE String to INT\r\nSELECT PARSE('1000' AS INT) AS 'String to INT'\r\n-- PARSE String to Numeric\r\nSELECT PARSE('1000.06' AS NUMERIC(8,2)) AS 'String to Numeric'\r\n-- PARSE String to DateTime\r\nSELECT PARSE('05-18-2013' as DATETIME) AS 'String to DATETIME'\r\n-- PARSE String to DateTime\r\nSELECT PARSE('2013\/05\/18' as DATETIME) AS 'String to DATETIME'\r\n-- PARSE string value in the India date format to DateTime \r\nSELECT PARSE('18-05-2013' as DATETIME using 'en-in') \r\n AS 'String in Indian DateTime Format to DATETIME'\r\n-- PARSE string value is in the US currency format to Money \r\nSELECT PARSE('$2500' as MONEY using 'en-US') \r\n AS 'String in US Currency Format to MONEY'<\/pre>\n
RESULT:<\/strong>
\n\"PARSE_SQL_SERVER_2012_CONVERSION_FUNCTION\"<\/a><\/p>\n
Below example demonstrates how PARSE function tries it’s best convert the input string value to\u00a0 a specified data type even when the specified value is not in the correct format.<\/strong><\/p>\n
SELECT PARSE('08-JUNE-2013' AS DATETIME)\r\nSELECT PARSE('08-JUN-2013' AS DATETIME)\r\nSELECT PARSE('2013JUNE08' AS DATETIME)\r\nSELECT PARSE('08\/JUN\/2013' AS DATETIME)<\/pre>\n
All the above statements will return the same result and it is:\u00a02013-06-08 00:00:00.000<\/em><\/p>\n
Now try to PARSE an invalid string value to DATETIME:<\/strong><\/p>\n
This conversion fails as February month will not have 31st day.<\/p>\n
--PARSE invalid String to DATETIME\r\nSELECT PARSE('2012\/02\/31' as DATETIME)\r\nGO<\/pre>\n
Result:
\nMsg 9819, Level 16, State 1, Line 1
\nError converting string value ‘2012\/02\/31’ into data type datetime using culture ”.<\/span><\/p>\n
Below PARSE function will fail to translate the date. Because the culture parameter specified is en-us, but the string value specified is not in US format. US Date format excepts month first instead of day and then day and year.<\/p>\n
--PARSE invalid String to DateTime\r\nSELECT PARSE('18-05-2013' as DATETIME using 'en-us')\r\nGO<\/pre>\n
Result:
\nMsg 9819, Level 16, State 1, Line 1
\nError converting string value ’18-05-2013′ into data type datetime using culture ‘en-us’.<\/span><\/p>\n
Difference between PARSE and CONVERT function.<\/strong><\/p>\n
Below example demonstrates the difference between PARSE and CONVERT function. PARSE function will successfully converts the string ‘Saturday, 08 June 2013’ to date time, but the CONVERT function fails to convert the same value. That is PARSE function tries it’s best to convert the input string value to the requested type, but CONVERT function requires the input string to be exact format no variations allowed.<\/p>\n
--PARSE Function Succeeds\r\nSELECT PARSE('Saturday, 08 June 2013' AS DATETIME) \r\n                 AS 'PARSE Function Result' GO\r\nSELECT PARSE('Sat, 08 June 2013' AS DATETIME) \r\n                 AS 'PARSE Function Result' GO\r\nSELECT PARSE('Saturday 08 June 2013' AS DATETIME) \r\n                 AS 'PARSE Function Result' GO\r\n\r\n--CONVERT Function Fails\r\nSELECT CONVERT(DATETIME, 'Saturday, 08 June 2013') \r\n                 AS 'CONVERT Function Result' GO\r\nSELECT CONVERT(DATETIME, 'Sat, 08 June 2013') \r\n                 AS 'CONVERT Function Result' GO\r\nSELECT CONVERT(DATETIME, 'Saturday 08 June 2013') \r\n                 AS 'CONVERT Function Result' GO<\/pre>\n
RESULT:<\/strong><\/p>\n
PARSE Function Result
\n———————–
\n2013-06-08 00:00:00.000<\/p>\n
PARSE Function Result
\n———————–
\n2013-06-08 00:00:00.000<\/p>\n
PARSE Function Result
\n———————–
\n2013-06-08 00:00:00.000<\/p>\n
CONVERT Function Result
\n———————–
\nMsg 241, Level 16, State 1, Line 3
\nConversion failed when converting date and\/or time from character string.<\/span><\/p>\n
CONVERT Function Result
\n———————–
\nMsg 241, Level 16, State 1, Line 1
\nConversion failed when converting date and\/or time from character string.<\/span><\/p>\n
CONVERT Function Result
\n———————–
\nMsg 241, Level 16, State 1, Line 1
\nConversion failed when converting date and\/or time from character string.<\/span><\/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":"
PARSE is one of the new built-in conversion function introduced as a Part of Sql Server 2012. PARSE function converts the string expression to the requested data type. It tries it’s best to translate the string\u00a0value to requested type but if it fails to\u00a0translate then raises an exception. Important Note: This PARSE function is not … Continue reading 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,98,96,95,35,94,97,986,55],"class_list":["post-968","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-parse","tag-difference-between-parse-and-convert","tag-differences-2","tag-new-feature-in-sql-server-2012","tag-parse","tag-parse-vs-conert","tag-sql-server","tag-sql-server-2012"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p3xNAz-fC","_links":{"self":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/968","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=968"}],"version-history":[{"count":23,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/968\/revisions"}],"predecessor-version":[{"id":992,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/posts\/968\/revisions\/992"}],"wp:attachment":[{"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/media?parent=968"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/categories?post=968"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhints.com\/wp-json\/wp\/v2\/tags?post=968"}],"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