Presents your 
SQL SERVER E-NEWSLETTER for June 24, 2003

<------------------------------------------->

USE CAST RATHER THAN CONVERT TO CONVERT DATA TYPES

There are two times when you must convert a SQL Server 2000 variable:
when data is being moved, compared, or combined with another object; or
when data is being moved into a variable.

If the different variable types support implicit conversion, you don't
have to do anything. SQL Server will do the conversion before it continues
with the operation. If, however, you have two data types that have an
explicit conversion relationship, you'll have to do the conversion yourself.

In that case, you have two options: the CAST function or the CONVERT
function. Though CAST and CONVERT essentially do the same things, CAST is
based on the SQL-92 standard, and it's the preferred method of data conversion.

As expressions go, CAST has a pretty simple syntax and can be used in
any LIKE or WHERE clause.

CAST (expression AS data_type)

In the following example, we'll use the pubs database and convert the
year-to-date integer in the titles table from an integer to a char(20) in
titles that have a 3 in the first position of the year-to-date sales.

USE pubs
GO
SELECT SUBSTRING(title, 1, 30) AS Title, ytd_sales
FROM titles
WHERE CAST(ytd_sales AS char(20)) LIKE '3%'
GO

Title                                         ytd_sales
--------------------------------------------------------
Cooking with Computers: Surrep                     3876
Computer Phobic AND Non-Phobic                      375
Emotional Security: A New Algo                     3336
Onions, Leeks, and Garlic: Coo                      375

CAST is also used for concatenating text and/or making text easier to
read by limiting how many characters show in the result set.

In the following example, we'll use the pubs database and titles table
again. This time, we're going to create output that is a sentence created
from varchar data types that list the prices of books over $10. To do
this, we first have to convert our money data type (the price) into a varchar.

USE pubs
GO
SELECT 'The price is ' + CAST(price AS varchar(12))
FROM titles
WHERE price > 10.00
GO
                         
-------------------------
The price is 19.99
The price is 11.95
The price is 19.99
The price is 19.99
The price is 22.95
The price is 20.00
The price is 21.59
The price is 10.95
The price is 19.99
The price is 20.95
The price is 11.95
The price is 14.99

(12 row(s) affected)

Now we're going to convert the title from a varchar to a char(50) so we
can limit the characters in the result set to 50. This makes it much more
readable on the screen. Notice that the title "Cooking with Computers:
Surreptitious Balance Sheets" has been cut off at 50 characters.

USE pubs
GO
SELECT CAST (title AS char(50)), ytd_sales
FROM titles
GO

Here is the result set:

Title                                                 ytd_sales
-----------------------------------------              ---------
The Busy Executive's Database Guide                        4095
Cooking with Computers: Surreptitious Balance Shee         3876
You Can Combat Computer Stress!                           18722
Straight Talk About Computers                              4095


Etc.

(18 row(s) affected)

J.E. Harvey, MCSD, MCDBA, has been tinkering, writing, and consulting
about technology for more than two decades.

----------------------------------------