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 FunctionThe syntax for CAST is:
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 ( expression as data_type )
and the produced result is
CAST('20100101' as datetime)
Now, you might wonder which are the data types.
The data typesYou need to use a proper data type in the CAST and in the CONVERT functions, so please consider the following list very carefully.
|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||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.|
|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.|
|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||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 functionAs said before, the CONVERT function is much more flexible and you can use it only in Sql Server.
The syntax is:
That is much more complicated, as you can see, compared to the CAST function. By the way, don't you hate those nesting brackets?
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
- data_type is the target data type;
- length is optional and it is used for nchar, nvarchar, char, varchar, binary or varbinary data types;
- expression is the data you want to convert;
- style is the style of the resulting value used to convert datetime, float, real, money and smallmoney.
The result will be:
CONVERT (varchar(4), 'This is a test')
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)|
|6||106||-||dd mon yy|
|7||107||-||Mon dd, yy|
|-||9 or 109||Default + milliseconds||mon dd yyyy hh:mi:ss:mmmAM (or PM)|
|-||13 or 113||Europe default + milliseconds||dd mon yyyy hh:mm: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|
For the float and real conversions:
|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:
|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.