Subtleties of Data-Type Precedence

A reader sent in the T-SQL query that Listing 1 shows because it didn't work as he expected. He was unable to concatenate 0 with the WHEN '1' branch of the CASE statement. The T-SQL code is supposed to explicitly convert the integer value '2004-09-24' to a char data type, but the result remains an integer value instead. I simplified the reader's original code to highlight the problem, so the T-SQL example might not seem valuable. But assume that you want a two-character string that represents the month you're testing for and you want to ensure the code prefixes a 0 to the month value for single-digit months, such as September, which the integer 9 represents. However, the sample SELECT statement returns 9 as the result set instead of 09.

The trouble with this T-SQL query stems from the CASE statement, which includes result expressions (the clauses after the WHEN keyword) that contain different data types. The SQL Server Books Online (BOL) entry for CASE explains that the data type of the entire CASE expression is "the highest precedence type from the set of types in result_expressions and the optional else_result_expression" and points to the Data Type Precedence entry for additional information.

The problem is that the first expression in the CASE statement is a char value, but the second expression is an int value because that's the data type of the DATEPART() function's result. SQL Server must choose one data type for the entire CASE expression. SQL Server implicitly converts the data type not chosen for the CASE data type as a whole into the chosen data type. BOL's Data

Type Precedence section covers the rules SQL Server follows when deciding which data type has precedence over another. SQL Server will choose the data type value that has the highest precedence as the data type for the CASE expression as a whole. According to these rules, int values are a higher precedence than char values. Therefore, SQL Server implicitly converts the first CASE expression (the WHEN '1' branch) to an integer. It's almost as if the code explicitly converts the interim value of 09 by using an operation such as CAST('09' AS int), which returns an int representation of 9 rather than a char representation of 09.

Knowing the rules of data-type precedence helps you avoid problems associated with implicit conversions by explicitly converting the second branch of the WHERE clause to a char data type, as the query in Listing 2 shows. Failing to understand the rules of data-type precedence leads to subtle T-SQL errors and incorrect result sets that are difficult to troubleshoot. Read the rules if you aren't familiar with them.

The preceding discussion about CASE and data-type precedence is helpful for many situations. However, in this case, you can avoid the CASE statement and data-type precedent problem by using a T-SQL expression that uses CAST() to append the 0 for single-digit months:

SELECT RIGHT('0' + CAST(DATEPART(Month, @Testdate) AS   VARCHAR(2)), 2)

Corrections to this Article: