Different data types in case expression

In this blog post titled as “Different data types in case expression”, we will explore the behavior of CASE expression when dealing with different data types in THEN part (true) and ELSE part (false) of CASE expression. We will limit the scope of discussion of this post to data conversion issue only. If you are interested to explore more about the CASE expression, visit this MSDN link.

What is the Return type of CASE expression

As per MSDN, CASE expression returns the highest precedence type from the given set of types in THEN and ELSE part (true and false part). To explore data type precedence, you can visit Data Type Precedence link here.

Lets play with a simple demo to see this return type in action. This demo is for test and may look a bit silly. In below SQL query, we have two variables, @RetValThen as DATETIME and @RetValElse as INT. Both variables are of different data types. As DATETIME data type has higher priority over INT data type, below CASE expression will return a DATETIME data type irrespective of the output of logical condition which may be true or false.

In case of true part returns

DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 1 THEN @RetValTHEN ELSE @RetValELSE END

Output

2015-03-10 00:00:00.000

In case false part returns

DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 0 THEN @RetValTHEN ELSE @RetValELSE END

Output

1900-01-02 00:00:00.000

In false part, instead of 1, we are getting “1900-01-02 00:00:00.000”. Because the default value for DATETIME data type is “1900-01-01 00:00:00.000” and when 1 will be cast as DATETIME, the value 1 will be added in DAY part of the default DATETIME value. We can check this from below query .

SELECT CAST (1 AS DATETIME)
--Output -- "1900-01-02 00:00:00.000"

Handling different data types in CASE

To handle such kind of scenarios, we can type cast both parts of the CASE expressions (THEN and ELSE) in same data type like below.

After type cast – In case true part returns

DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 1 THEN CAST(@RetValTHEN AS VARCHAR(50)) ELSE CAST(@RetValELSE AS VARCHAR(50)) END

Output

2015-03-10 00:00:00.000

After type cast – In case false part returns

DECLARE @RetValThen DATETIME = '20150310'
DECLARE @RetValElse INT = 1
SELECT CASE WHEN 1 = 0 THEN CAST(@RetValTHEN AS VARCHAR(50)) ELSE CAST(@RetValELSE AS VARCHAR(50)) END

Output
1

Few more conversions with different data types in case expression and their output;

SELECT CASE WHEN 1 = 1 THEN 1 ELSE 1.1 END
--Output -> 1.0

SELECT CASE WHEN 1 = 1 THEN 'String' ELSE 0 END
--Output -> Error -> Conversion failed when converting the varchar value 'String' to data type int.

SELECT CASE WHEN 1 = 1 THEN '' ELSE GETDATE() END
--Output -> 1900-01-01 00:00:00.000

We have to type cast both parts of the CASE expression as same data type in all above examples to make them work.

At the end, I would ask you to share your method to achieve this goal. You can share your view in comment section.

Thanks for your reading and do share your feedback on this blog post.