{"id":6038,"date":"2013-07-07T00:00:56","date_gmt":"2013-07-07T00:00:56","guid":{"rendered":"http:\/\/craftydba.com\/?p=6038"},"modified":"2016-04-21T21:32:13","modified_gmt":"2016-04-21T21:32:13","slug":"conversion-functions-parse","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=6038","title":{"rendered":"Conversion Functions &#8211; PARSE()"},"content":{"rendered":"<p>I am going to continue the series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language.<\/p>\n<p>One analogy of a data <a href=\"http:\/\/en.wikipedia.org\/wiki\/Type_conversion\">type conversion<\/a> is apply the correct action to change the physical form of water (H2O).  You can convert water to ice by cooling it.  You can convert water to vapor by heating it.  However, the chemical composition of water is still the same!<\/p>\n<p>Information is stored in a database as rows in a table.  Each field in a table is defined with a certain data type.  The original designer of the database schema might not be aware of all potential uses of the stored information.  Therefore, it is not uncommon as a developer to <a href=\"http:\/\/en.wikipedia.org\/wiki\/Type_conversion\">convert data<\/a> from one type to another. <\/p>\n<p>Today, I want to talk about how to use the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh213316.aspx\">PARSE<\/a> function which takes a source string, a target data type, and a optional culture information as parameters.  It returns the target data type containing the transformed source string.<\/p>\n<p>The TSQL example below shows valid and invalid conversions.  Since I am not familiar with the presentation of German dates and times, I used the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh213505.aspx\">FORMAT<\/a> function to create me the strings for use in my code.<\/p>\n<pre class=\"lang:TSQL theme:familiar mark:1,2-3\" title=\"parse() - conversion functions\">\r\n--\r\n-- Convert strings w\/ culture info 2 data types using PARSE()\r\n--\r\n\r\n-- Passes since write culture\r\nSELECT PARSE('\u00a345.99' AS MONEY USING 'en-GB') AS COST_OF_TOY1;\r\n\r\n-- Fails since wrong culture\r\nSELECT PARSE('\u00a345.99' AS MONEY USING 'en-US') AS COST_OF_TOY2;\r\n\r\n-- Declare variables\r\nDECLARE @VAR_DE_DATE VARCHAR(32);\r\nDECLARE @VAR_DE_TIME VARCHAR(32);\r\n\r\n-- Store german date\/time\r\nSELECT @VAR_DE_DATE = FORMAT(SYSDATETIME(), 'D', 'de') \r\nSELECT @VAR_DE_TIME = FORMAT(SYSDATETIME(), 'G', 'de') \r\n\r\n-- Show our german strings\r\nSELECT @VAR_DE_DATE AS GERMAN_FULLDATE_STR\r\nSELECT @VAR_DE_TIME AS GERMAN_DATETIME_STR\r\n\r\n-- Convert to english data types\r\nSELECT PARSE(@VAR_DE_DATE AS DATE USING 'de') AS ENGLISH_DATE;\r\nSELECT PARSE(@VAR_DE_TIME AS TIME USING 'de') AS ENGLISH_TIME;\r\n\r\n<\/pre>\n<\/p>\n<p>The output from the TSQL example is shown below.<\/p>\n<pre class=\"lang:TSQL theme:epicgeeks\" title=\"output\">\r\noutput: \r\n\r\nCOST_OF_TOY1\r\n---------------------\r\n45.99\r\n\r\nCOST_OF_TOY2\r\n---------------------\r\nMsg 9819, Level 16, State 1, Line 2\r\nError converting string value '\u00a345.99' into data type money using culture 'en-US'.\r\n\r\n\r\nGERMAN_FULLDATE_STR\r\n--------------------------------\r\nDienstag, 23. Juli 2013\r\n\r\nGERMAN_DATETIME_STR\r\n--------------------------------\r\n23.07.2013 15:47:24\r\n\r\n\r\nENGLISH_DATE\r\n------------\r\n2013-07-23\r\n\r\nENGLISH_TIME\r\n----------------\r\n15:47:24.0000000\r\n\r\n<\/pre>\n<\/p>\n<p>Here are the results from executing the TSQL example.  <\/p>\n<ol>\n<li>\nI was able to convert \u00a345.99 pounds to a money data type when using the Great Britain cultural format.  However, it fails when using the English cultural format.  We use dollars in America!\n<\/li>\n<li>\nI was able to generate both a full date string as well as a numeric date string in the German language.\n<\/li>\n<li>\nUsing German as the cultural format, I was able to convert the prior strings to both a DATE and TIME data type.\n<\/li>\n<\/ol>\n<p>In summary, the PARSE function is used to convert strings to other data types.  Cultural specific information for money, date, or time formatting can be accounted for.<\/p>\n<p>Next time, I will be talking about the <a href=\"http:\/\/msdn.microsoft.com\/en-us\/library\/hh230993.aspx\">TRY_CONVERT<\/a> function.  This function was introduced in SQL Server 2012.  Unlike its cousin, invalid casting of data results in a NULL value instead of an ERROR.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I am going to continue the series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language. One analogy of a data type conversion is apply the correct action to change the physical form of water (H2O). You can convert water to ice by cooling it. You can convert water to vapor by heating it. However, the chemical composition of water is still the same! Information is stored in a database as rows in a table. Each field in a table is defined with a certain&hellip;<\/p>\n","protected":false},"author":1,"featured_media":6008,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[814],"tags":[892,893,31,895,362,894,15,897,28,29,891],"class_list":["post-6038","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-conversion-functions","tag-conversion-matrix","tag-database-developer","tag-explicit-conversion","tag-format","tag-implicit-conversion","tag-john-f-miner-iii","tag-parse","tag-sql-server","tag-tsql","tag-type-conversion"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6038","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=6038"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6038\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/media\/6008"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=6038"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6038"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6038"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}