I am going press on with my series of articles (tidbits) on type conversion functions available in the Transaction SQL (T-SQL) language.<\/p>\n
One analogy of a data 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 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 convert data<\/a> from one type to another. <\/p>\n Today, I want to talk about how to use CAST<\/a> function which takes a source data value and a target data type as parameters. It returns the the source data value casted to the target data type.<\/p>\n The TSQL example below shows valid and invalid conversions for strings, numbers, and dates.<\/p>\n The output from the TSQL example is shown below.<\/p>\n Here are the results from executing the TSQL example. <\/p>\n Please note the CAST<\/a> function uses the following matrix<\/a> to transform data from one form to another. <\/p>\n\r\n--\r\n-- Format variables using CAST()\r\n--\r\n\r\n-- Famous quote\r\nDECLARE @VAR_QUOTE VARCHAR(128) = \r\n'It is the mark of an educated mind to be able to \r\nentertain a thought without accepting it - Aristotle';\r\n\r\n-- Full quote\r\nSELECT @VAR_QUOTE AS FULL_QUOTE;\r\n\r\n-- Unexpected truncation\r\nSELECT CAST(@VAR_QUOTE AS CHAR(34)) AS SHORT_QUOTE;\r\n\r\n-- My 64 K number\r\nDECLARE @VAR_NUMBER INT = POWER(2, 16);\r\n\r\n-- Full number\r\nSELECT @VAR_NUMBER AS LARGE_NUMBER;\r\n\r\n-- Overflow when converting \r\nSELECT CAST(@VAR_NUMBER AS SMALLINT) AS SHORT_NUMBER;\r\n\r\n-- Date as hex numbers\r\nDECLARE @VAR_BYTE AS BINARY(4) = 0x20130723;\r\n\r\n-- Conversion is allowed\r\nSELECT CAST('20130723' as DATE) AS RIGHT_DATE\r\n\r\n-- Conversion is not allowed\r\nSELECT CAST(@VAR_BYTE as DATE) AS WRONG_DATE\r\n\r\n<\/pre>\n<\/p>\n\r\noutput: \r\n\r\nFULL_QUOTE\r\n-----------------------------------------------------\r\nIt is the mark of an educated mind to be able to \r\nentertain a thought without accepting it - Aristotle\r\n\r\nSHORT_QUOTE\r\n-----------------------------------------------------\r\nIt is the mark of an educated mind\r\n\r\nLARGE_NUMBER\r\n------------\r\n65536\r\n\r\nSHORT_NUMBER\r\n------------\r\nMsg 220, Level 16, State 1, Line 8\r\nArithmetic overflow error for data type smallint, value = 65536.\r\n\r\nRIGHT_DATE\r\n----------\r\n2013-07-23\r\n\r\nWRONG_DATE\r\n----------\r\nMsg 241, Level 16, State 1, Line 3\r\nConversion failed when converting date and\/or time from character string.\r\n\r\n<\/pre>\n<\/p>\n
\n
![matrix<\/a> to transform data from one form to another. <\/p>\nNext time, I will be talking about the PARSE<\/a> function that 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","protected":false},"excerpt":{"rendered":"I am going press on with my 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…<\/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":[896,892,893,31,895,894,15,28,29,891],"class_list":["post-6025","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-very-short-articles","tag-cast","tag-conversion-functions","tag-conversion-matrix","tag-database-developer","tag-explicit-conversion","tag-implicit-conversion","tag-john-f-miner-iii","tag-sql-server","tag-tsql","tag-type-conversion"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6025","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=6025"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/6025\/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=6025"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=6025"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=6025"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}](\"https:\/\/craftydba.com\/wp-content\/uploads\/2013\/07\/sql-server-conversion-matrix.gif\"){kind=link}