Wednesday 1 December 2010

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

   


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!

0 thoughts:

Post a Comment

Comments are moderated. I apologize if I don't publish comments immediately.

However, I do answer to all the comments.