Cast Your Data
February 18, 2001
Cast That Data About!
Most of the SQL Server DBAs I know use the CONVERT to change datatypes in their SQL code. However, this is not an ANSI standard and limits the portability. While I rarely see databases moving from SQL Server to some other RDBMS, I have seen it happen. Also, as more SQL Server databases grow, there will be more and more DBAs that come from other products to work with SQL Server. These DBAs will likely write SQL code that is more SQL-92 standard and will not include the T-SQL legacy code words.
In order to ensure your code can be maintained by others as well build your ANSI skills, I recommend beginning to convert some of your code to adhere to SQL-92 standards. One SQL function that you should become familiar with is the CAST function.
Essentially CAST performs the same function as CONVERT, namely changing data from one datatype to another. The basic format of the CAST function is as follows (from the latest Books Online in SQL Server 2000 Beta 2):
As you may have noticed, the CONVERT function has more options than CAST. There are instances where CONVERT is needed, but in general according to Books Online:
"CAST is based on the SQL-92 standard and is preferred over CONVERT."
If you notice the fourth footnote, it mentions that there are a few cases where CONVERT can cause a loss of precision and that CAST is required. These cases are the four cases for decimal/numeric to decimal/numeric conversions.
At TechEd this year, all of the demonstrations given regarding the new sql_variant datatype and books online demonstrate the requirement to pick a datatype when performing operations on data of this datatype. Each of these demonstrations was given using CAST as the example function. This reason alone is worth learning to use CAST since most sample code from the SQL team in Redmond will likely use this function.
When to use CONVERT
When you need a style for the new datatype, then CONVERT is required since CAST does not support this functionality. If you check the Books Online CONVERT page, you will see the following list of styles:
Also, in the Oracle data conversion section of Books Online, there are two places where CONVERT is shown as an example rather than CAST.
Some examples of using CAST are shown below. These are mainly simple data type conversions that I have used in the past.
1. Convert an character string into a integer. Keep in mind the string must be only letters.
declare @c char( 10), @i int select @c = '125' select @i = cast( @c as INT)
2. Convert an character string into a date. Keep in mind the string must be in a recognizable date format.
declare @c char( 10), @d datetime select @c = '09/15/2000' select @d = cast( @c as datetime)
3. Convert a float character string into a string.
declare @c char( 20) select @c = cast( pi() as char(20 ))
There are many ways in which CAST can be used and many fine examples in Books Online. You should check there for more examples. If there are specifics that you would like to see, please email me and I will add them to this article.