Data Type Conversion in Expressions

There are two types of data conversion:

• Implicit conversions, in which the expression evaluator automatically converts the data from one data type to another. For example, if a smallint is compared to an int, the smallint is implicitly converted to int before the comparison is performed.
• Explicit conversions, in which you use casting to convert a value, such as a local variable, a column, or another expression, from one data type to another. For more information, see Cast (SSIS).

The following diagram shows the result type of implicit conversions of BINARY operations. The intersection of column and row in this table is the result type of a binary operation with operands of the left (From) and right (To) types.

The intersection of a signed and an unsigned integer is a signed integer that is potentially larger than either argument.

Operations that combine an unsigned eight-byte integer and a signed integer require an explicit cast in order to clarify the result format.

A function that uses a single argument returns a result with the same data type as the argument, with the following exceptions:

• DAY, MONTH, and YEAR accept a date and return an integer (DT_I4) result.
• ISNULL accepts an expression of any SSIS data type and returns a Boolean (DT_BOOL) result.
• SQUARE and SQRT accept a numeric expression and return a non-integral numeric (DT_R8) result.

If the arguments have the same data type, the result is of that type. The only exception is the result of a binary operation on two values with the DT_DECIMAL data type, which returns a result with the DT_NUMERIC data type.

Functions also compare strings, dates, and Booleans. Before a function compares two values, the expression evaluator performs the following implicit conversions:

• Strings with a DT_STR data type are converted to the DT_WSTR data type.
• Date and time values are converted to the DT_DBTIMESTAMP data type.

The expression evaluator cannot implicitly convert any value to a Boolean. Instead the expression must include an explicit cast operation. For more information, see Cast (SSIS).

Note

Boolean values are logical values, not numbers. Although Boolean values may be displayed as numbers in some environments, they are not stored as numbers, and various programming languages represent Boolean values as numeric values differently, as do the .NET Framework methods. For example, the conversion functions available in Visual Basic convert True to -1; however, the System.Convert.ToInt32 method in the .NET Framework converts True to +1. The Integration Services Expression Language converts True to -1. To avoid errors or unexpected results, you should not write code that relies on particular numeric values for True and False. Wherever possible, you should restrict usage of Boolean variables to the logical values for which they are designed.

An expression can use arguments and operands that have incompatible data types, and in that case the expression evaluator cannot perform implicit data conversion. For example, the expression evaluator does not allow the comparison of an operand with a string data type to an operand with a numeric data type. To perform this comparison, the numeric operand must be explicitly cast to a string data type. The expression language includes a cast operator that supports casting between string, numeric, date, and binary data types. For more information, see Cast (SSIS).

Change History

Release	History
17 July 2006	Changed content: Provided tips about working with Boolean values.

See Also

Other Resources

Integration Services Expression Concepts

Help and Information

Getting SQL Server 2005 Assistance