The convert() Function

In order to combine columns and constants, they must be of the same underlying data type. The server will convert varchar and char columns without your help. However, if you tried a query where you wanted a numeric and a character type in the same column, you would have a problem.

select     title_id + “ costs $” + price
from       titles

Msg   257, Level 16, State 1
Implicit  conversion  from  datatype  ‘varchar’  to  ‘money’  is  not
allowed.
  Use the CONVERT function to run this query.

To overcome this, as the error message suggests, you must use the CONVERT() function.

The CONVERT function allows you to convert data types from one type to another. This is useful for more applications than the current example. You can, for example, convert character data to numeric data for use in mathematical calculations. In the current example, in order to perform the concatenation, convert the money data to variable character format:

select   title_id  +  “  costs  $”  +  convert(varchar(10),  price)
from     titles

------------------------
BU1032 costs $19.99
BU1111 costs $11.95
[…]
TC3218 costs $20.95
TC4203 costs $11.95
TC7777 costs $14.99

(18 row(s) affected)

The CONVERT() function allows conversion from one data type to another. In this example, in order to concatenate a money type (the price column) to a character type (the title_id column) the CONVERT function was used on the price column.

In the last example, I converted the price column, containing small money data, to a variable character. When a data type is converted to a variable character, the length of the resultant string is only as long as it needs to be in order to fit the entire string, up to the maximum specified in parentheses. In this case, I asked for a varchar(10). So if the price is 20.95, the converted varchar string would be exactly five characters long.

On the other hand, if I had converted the price to a fixed-length character string (a char(10)), the total string length would have been 10 characters no matter how many characters were required to fit the string. The data is placed flush left, and spaces are added on the end to round out the rest of the character field.

In either case, if the price data were too large to fit in the maximum length of the field—the price of technical books, like this one, is going up all the time, and the day when you pay $1,000,000.00 can’t be far off—the server will place an asterisk in the character field to indicate that it didn’t have enough room to convert the numeric data.

If you convert a long string to a shorter string, the data is truncated, meaning that as much of the string as fits in the new length is converted, and the rest is discarded. This can be useful for especially long columns, such as the title column.

select     convert(char(12), title) “Short Title”, price
from       titles

Short Title   price
------------           --------------------------
The Busy Exe  19.99
Cooking with  11.95
You Can Comb  2.99
[…]
Fifty Years  11.95
Sushi, Anyon 14.99

(18  row(s)  affected)

This is the syntax for using CONVERT to manipulate data types:

convert  (<data  type>  [(optional  length)],  <expression>  )

If you do not specify a length for data types, such as char and varchar fields, the server defaults them to a data type–specific value. This is 30 characters for char and varchar types. Form a good habit and always specify the length of fields.

Using convert to Format Dates

The CONVERT function serves a dual purpose. In addition to converting data types, it also formats date fields in a variety of different ways. By default, when requesting a datetime value, you receive output like this:

Dec 5 1996 10:45AM

As you know, datetime values store values out to the millisecond. If you wanted to see the whole datetime field, you would use the CONVERT function to request an alternative format. I use the GETDATE() function here to get the current date and time. Later today I’ll talk about that particular function.

select  convert(varchar(40),  getdate(), 9)

----------------------------------------
Dec  5 1996 10:47:14:656AM

When using CONVERT to request alternative date formats, always convert the data to a char or varchar data type. Conversion back to a datetime defaults the display to the regular method.

Table 4.2 summarizes the available date formats usable with CONVERT(). These codes display the year without a century prefix. To get a century prefix, add 100 to the code. Note that styles 9 and 13 always provide a four-digit year.

SQL Server Functions

There are four types of built-in functions that you can use in T-SQL. The types of functions are mathematical functions, string functions, system functions, and niladic functions. I’ll provide an example of each function in the next section. Niladic functions work exclusively with the INSERT statement, so I’ll wait until then to deal with them. They include functions such as CURRENT_TIMESTAMP to insert the current date and time, automatically.