Using the general purpose conversion function: convert

The general conversion function, convert, converts between a variety of datatypes and specifies a new display format for date and time information. Its syntax is:

convert(datatype, expression [, style] ) 

Here is an example that uses convert in the select list:

select title, convert(char(5), total_sales) 
from titles 
where type = "trad_cook" 
title 
------------------------------------     ----- 
Onions, Leeks, and Garlic: Cooking 
    Secrets of the Mediterranean           375 
Fifty Years in Buckingham Palace 
    Kitchens                             15096 
Sushi, Anyone?                            4095 
 
(3 rows affected) 

In the following example, the total_sales column, an int column, is converted to a char(5) column so that it can be used with the like keyword:

select title, total_sales 
from titles 
where convert(char(5), total_sales) like "15%" 
    and type = "trad_cook"
title 
---------------------------------        ----- 
Fifty Years in Buckingham Palace
    Kitchens                             15096 
 
(1 row affected)

Certain datatypes expect either a length or a precision and scale. If you do not specify a length, Adaptive Server uses the default length of 30 for character and binary data. If you do not specify a precision or scale, Adaptive Server uses the defaults of 18 and 0, respectively.