SQL Server converts data types in two ways -- the way it wants to and the way you tell it to. Most implicit conversions work the way you expect. It's the surprise conversions that cause trouble. Unless you know exactly how SQL Server converts a data type, I recommend that you explicitly convert them. Most of the time you'll use CAST or CONVERT to control the conversion outcome.
Note: This information is also available as a PDF download.
#1: CAST to eliminate format
Use CAST, as follows, to explicitly convert an expression when you don't want or need to format the results:
CAST(expression AS datatype)
Strictly speaking, you probably won't convert just to eliminate a format, but it's nice to know you can eliminate formatting and convert in one step. There's one catch: SQL Server must support datatype; this function doesn't support user-defined data types.
Visit http://msdn2.microsoft.com/en-us/library/ms187752.aspx to learn more about SQL Server data types.
#2: CONVERT to format
CONVERT lets you convert and format in one step as follows, where style specifies the format:
CONVERT (datatype, expression, style)
Like CAST, datatype must be system-supported, as it won't work with user-defined data types.
Visit http://msdn2.microsoft.com/en-us/library/ms187928.aspx to view a list of style values.
#3: CAST for decimalsIf you let SQL Server choose, there's no guarantee that it will return the data type you expect when evaluating data. For instance, the statement in Figure A returns an integer instead of the exact number because SickLeaveHours is an int column.
Figure A: The column's int data type truncates the decimal portion.
Compare the above results to those in Figure B. By converting SickLeaveHours values to a numeric data type before evaluating them, the expression returns the decimal component. Converting the expression's results won't work because it's too late.
Figure B: Convert a value before evaluating it in an expression for best results.
#4: CAST to truncate
When trying to convert decimal values into integers, you might start with ROUND. However, that function doesn't always get the job done. For instance, suppose your shipping volume is so great that your distribution company charges by the smaller whole unit -- forgiving the overage into the next pound. In other words, if the item weighs 2.5 pounds, the company charges for only 2.In this case, you'd need to know the shipping weight and not necessarily the item's true weight. As you can see in Figure C, this is one of those times when ROUND doesn't work. It rounds down in some cases, which works. In other cases, it rounds up, and you'd pay more money than necessary.
Figure C: ROUND doesn't always get the job done.
As you can see in Figure D, CAST solves the problem. In this case, converting the weight value to an integer truncates the decimal portion.
Figure D: CAST to truncate a value's decimal value.
#5: CAST and CONVERT to concatenateSQL Server won't concatenate values of different data types. Furthermore, concatenation is strictly a string function. That means you must convert all values to strings before SQL Server will concatenate them. Figure E shows the error returned when an expression attempts to concatenate a numeric value (Weight) to a string.
Figure E: SQL Server can't concatenate values of different data types.
For concatenation to work, use CAST to convert the numeric value to a string, as shown in Figure F. Once the numeric weight value is a string, SQL Server can concatenate properly.
Figure F: Convert values to string before concatenating.
CONVERT works almost interchangeably with CAST in regards to concatenation. Figure G shows the same concatenation expression using CONVERT instead of CAST.
Figure G: When concatenating CAST and CONVERT are both on equal ground.
#6: CAST and CONVERT variants -- always
As a general rule, you should avoid using SQL Server's sql_variant data type. Besides being a memory hog, sql_variant is limited:
- Variants can't be part of a primary or foreign key.
- Variants can't be part of a computed column.
- Variants won't work with LIKE in a WHERE clause.
- OLE DB and ODBC providers automatically convert variants to nvarchar(4000) -- ouch!
To avoid problems, always explicitly convert sql_variant data types as you use them. Use any method you please, just don't try to work with an unconverted sql_variant data type.
#7: CONVERT for date componentsFunctions won't always return a datetime value just because the expression evaluates a datetime data type. SQL Server is generally smart enough to get the implicit conversion correct. As you can see in Figure H, with or without converting, the resulting values are correct -- depending upon your needs.
Figure H: When in doubt, convert datetime data types to get exactly what you need.
This conversion decision is similar to #4 and #5. The problem is, you might not expect to have this problem with a datetime data type.
#8: CONVERT datetime to a stringIt's impossible to get rid of the date or the time component in a datetime data type, but you can work with the date and time values separately. One way to get the date is to convert the datetime value to an unformatted string, as shown in Figure I. The good news is that SQL Server correctly interprets the unformatted string, regardless of datetime or language settings, when you convert the string back to datetime.
Figure I: Sometimes a formatted date string is easier to work with than a datetime value.
#9: CAST for quicker search
If a numeric search or sort is slow, try converting to a character data type to speed things up. This trick won't always be appropriate, but when it works, it works great. Simply convert the searched numeric column in the WHERE clause as follows:
WHERE CAST(column AS char(n)) condition
For example, the conversion in the following WHERE clause improves server statistics:
SELECT * FROM HumanResources.EmployeeSick
WHERE CAST(SickLeaveHours AS char(3)) <> 0
You'll need a lot of records to notice the improvement, and it won't always work. To view statistics for a specific query, choose Query Options from the Query menu (in Management Studio). Expand the Advanced node, check SET STATISTICS TIME and SET STATISTICS IO, and click OK. Run the query and compare statistics listed in the Messages tab.
#10: CAST for quick date fixWhen date values aren't datetime data types, you can still depend on date arithmetic if you convert the strings first, as shown in Figure J.
Figure J: Convert date values to evaluate date values in simple expressions.
Use CONVERT when the string format is ambiguous, such as mm/dd/yyyy and dd/mm/yyyy.
A word of warning
It's important to note something that seems obvious but that we often forget. Use converted results for only the purpose intended. It's just too easy to forget about the conversion and in doing so, to generate subsequent errors that are hard to debug.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.