SQL Server: basic functions (part 3) - Cast and Convert

Tweet

Following the other 2 parts of the series, in this short article we are going to see how to convert data in a query.

Cast and Convert: why 2 functions?
Cast and Convert are two functions used to convert data. I've always been confused by the fact that there are actually 2 functions to - apparently - perform the same operation. First of all, I must say that CONVERT can be used only in Sql Server while CAST derives from the ANSI standard. Wow! And so? CONVERT is undoubtedly more flexible while CAST is in a way a standard, it is more portable (it works in almost in any db environment), but it is less powerful. On top of that, CAST is preferably used when converting between decimal and numeric values to preserve the number of decimal places.

The Cast Function

The syntax for CAST is:

CAST ( expression as data_type )

where expression is the data you want to convert, and data_type is the type of data you want to convert the expression into. Simple as that! An example could be:

CAST('20100101' as datetime)

and the produced result is

2010-01-01 00:00:00.000

Now, you might wonder which are the data types.

The data types

You need to use a proper data type in the CAST and in the CONVERT functions, so please consider the following list very carefully.

Integers
bigint	Integer (whole number) data from -2^63 (-9,223,372,036,854,775,808) through 2^63-1 (9,223,372,036,854,775,807).
int	Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647).
smallint	Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767).
tinyint	Integer data from 0 through 255.
bit
bit	Integer data with either a 1 or 0 value.
decimal and numeric
decimal	Fixed precision and scale numeric data from -10^38 +1 through 10^38 –1.
numeric	Functionally equivalent to decimal.
money and smallmoney
money	Monetary data values from -2^63 (-922,337,203,685,477.5808) through 2^63 - 1 (+922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit.
smallmoney	Monetary data values from -214,748.3648 through +214,748.3647, with accuracy to a ten-thousandth of a monetary unit.
Approximate Numerics
float	Floating precision number data with the following valid values: -1.79E + 308 through -2.23E - 308, 0 and 2.23E + 308 through 1.79E + 308.
real	Floating precision number data with the following valid values: -3.40E + 38 through -1.18E - 38, 0 and 1.18E - 38 through 3.40E + 38.
datetime and smalldatetime
datetime	Date and time data from January 1, 1753, through December 31, 9999, with an accuracy of three-hundredths of a second, or 3.33 milliseconds.
smalldatetime	Date and time data from January 1, 1900, through June 6, 2079, with an accuracy of one minute.
Character Strings
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
text	Variable-length non-Unicode data with a maximum length of 2^31 - 1 (2,147,483,647) characters.
Unicode Character Strings
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters. sysname is a system-supplied user-defined data type that is functionally equivalent to nvarchar(128) and is used to reference database object names.
ntext	Variable-length Unicode data with a maximum length of 2^30 - 1 (1,073,741,823) characters.
Binary Strings
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
image	Variable-length binary data with a maximum length of 2^31 - 1 (2,147,483,647) bytes.
Other Data Types
cursor	A reference to a cursor.
sql_variant	A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
table	A special data type used to store a result set for later processing.
timestamp	A database-wide unique number that gets updated every time a row gets updated.
uniqueidentifier	A globally unique identifier (GUID).

Have you ever thought we had so many data types which to choose from?

The Convert function

As said before, the CONVERT function is much more flexible and you can use it only in Sql Server.
The syntax is:

CONVERT ( data_type [ ( length ) ] , expression [ , style ] )

That is much more complicated, as you can see, compared to the CAST function. By the way, don't you hate those nesting brackets?
Anyway,  

1. data_type is the target data type;
2. length is optional and it is used for nchar, nvarchar, char, varchar, binary or varbinary data types;
3. expression is the data you want to convert;
4. style is the style of the resulting value used to convert datetime, float, real, money and smallmoney.

Talking about the length parameter is quite clear that when converting something into - for example - varchar you can specify the length:

CONVERT (varchar(4), 'This is a test')

The result will be:

This

For the style parameter, you can choose from three lists of values according to the type of data you need as a result.
For datetime and smalldatetime conversions:

Without century (yy)	With century (yyyy)	Standard	Input/Output
-	0 or 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 or 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	ISO	yymmdd
-	13 or 113	Europe default + milliseconds	dd mon yyyy hh:mm:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 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	Hijri	dd mon yyyy hh:mi:ss:mmmAM
-	131	Hijri	dd/mm/yy hh:mi:ss:mmmAM

For the float and real conversions:

Value	Output
0 (default)	Six digits maximum. Use in scientific notation, when appropriate.
1	Always eight digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.

For money and smallmoney conversions:

Value	Output
0 (default)	No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1	Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2	No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

** The tables are taken from the MSDN Library **

That's a lot of information, I guess. Unfortunately, those are the options and I thought this post would have been incomplete, not to mention them all.

Surely this article is not really revealing everything on CAST and CONVERT, however I always found the above information sufficient to use them in queries. Specific conversion problems are always waiting round the corner - I know - but most of the time, errors are due to the fact that we are trying to convert something into an incompatible target data type.
As a reference, you can look at the chart provided by MSDN to see allowed conversions.


Every time you encounter an error in conversion, please read carefully the above tables and most of the time you will understand why things are not working.

Next time I would like to talk about simpler functions manipulating dates.

Happy programming!