Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Cast Your Data

By Steve Jones,

Cast That Data About!

Most of the SQL Server DBAs I know use the CONVERT to change data types 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 data type to another. The basic format of the CAST function is as follows (from the latest Books Online in SQL Server 2000:

CAST and CONVERT
Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality Here is the syntax :

Syntax
Using CAST:
CAST ( expression AS data_type)
Using CONVERT:
CONVERT(data_type[ ( length)], expression [,style] )
Arguments
expression
Is any valid Microsoft® SQL Server(tm) expression.
data_type Is the target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used.

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."
In addition, if you reference the conversion chart that is supplied in Books Online, you will see the following chart:

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 Tech Ed 2000, all of the demonstrations given regarding the new sql_variant data type and books online demonstrate the requirement to pick a data type when performing operations on data of this data type. 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 data type, 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:

Without century (yy) With century (yyyy) Standard Input/Output**
0 100 (*) Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - mon dd yy
8 108 - hh:mm:ss
9 109 (*) Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 IS yymmdd
13 113 (*) Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
20 120 (*) ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
21 121 (*) ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 ISO8601 yyyy-mm-dd Thh:mm:ss:mmm(no spaces)
- 130* Kuwaiti dd mon yyyy hh:mi:ss:mmmAM
- 131* Kuwaiti dd/mm/yy hh:mi:ss:mmmAM


* The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
** Input when converting todatetime; Output when converting to character data.

Also, in the Oracle data conversion section of Books Online, there are two places where CONVERT is shown as an example rather than CAST.
        1.Conversions of date to char (because of styles)
        2.Conversions of Binary to Hex

Examples:

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 ))

Conclusion

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.

As always I welcome feedback on this article using the "Your Opinion" button below. Please also rate this article.

Steve Jones
©dkRanch.net March 2002

Return to Steve Jones Home

Total article views: 16501 | Views in the last 30 days: 12
 
Related Articles
ARTICLE

CLR Table-Valued Function Example with Full Streaming (STVF / TVF)

SQL CLR Table-Valued Functions can stream data back, but there aren't many examples of the quick and...

BLOG

Cast vs Convert

Sometimes you must "convert" a column or variable from one data type to another.  For example, if yo...

BLOG

Metadata Functions (OBJECT_NAME for example)

What is a metadata function? According to BOL a metadata function is one that returns data about th...

FORUM

-dynamic- function to convert several rows of a table to a csv list

-dynamic- function to convert several rows of a table to a csv list

FORUM

How to convert an Access Last function to SQL Server

convert an Access Last function to SQL Server

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones