THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

implicit data type conversion and COALESCE

Implicit data type conversion can cause both unexpected and undesirable results. Using the same design pattern for one data type and extending it to other data types can get you into trouble if implicit data type conversion occurs.

The following code sample below was inspired by actual legacy code that was intended to build a string showing the values of variables in a stored procedure. The desired output would be something similar to this and was constructed using string concatenation:

             @xDATETIME 3-Mar-2013 @xINT 23 @xNVARCHAR something

The problem with building SQL strings using string concatenation is that if any substring is NULL, the entire concatenated string is NULL. COALESCE can fix that problem when the data type is some type of string. The first SELECT demonstrates this.

But the design pattern for dealing with a NULL string variable doesn't extend to other data types as the second and third SELECT statements demonstrate. The DATETIME and INT variables don't get converted to strings (an empty string in this example). Instead, the empty string gets converted to DATETIME or INT, respectively. This conversion yields some unexpected results. The SQL Server documentation here does provide a clue about what happens when an empty string is converted to DATETIME. When a date value is not provided during a data type conversion, SQL Server defaults to 1900-01-01. Clearly a NULL does not provide a date value. Similarly, when a time value is not provided during a conversion, SQL Server defaults to 00:00:00.

I included explicit CAST statements to prove what happens when an empty string is converted to DATETIME or INT.

The solution is to avoid the implicit data type conversions in the first place. CAST the variables to strings first and then pass those strings to COALESCE as shown in the last two statements.

If you run the code, it's easy to see for yourself what's actually going on. 


declare @xINT      INT;

declare @xNVARCHAR NVARCHAR(10);


select COALESCE(@xNVARCHAR,'');  --returns an empty string


--implicit data type conversions cause unexpected results

select COALESCE(@xDATETIME,'');  --returns 1900-01-01 00:00:00.000

select COALESCE(@xINT,'');       --returns 0

--find out what data types the COALESCE statements become
select ISDATE(COALESCE(@xDATETIME,''));  --returns 1 indicating a date value
select ISNUMERIC(COALESCE(@xINT,''));    --returns 1 indicating a numeric value

--explicit data type conversions
select CAST('' AS DATETIME);  --returns 1900-01-01 00:00:00.000
select CAST('' AS INT);       --returns 0

--how to prevent COALESCE from doing  implicit data type conversion
select COALESCE(CAST(@xDATETIME AS NVARCHAR(23)),'');  --returns an empty string
select COALESCE(CAST(@xINT      AS NVARCHAR(23)),'');  --returns an empty string


Published Sunday, March 17, 2013 4:11 PM by John Paul Cook


No Comments
Anonymous comments are disabled

About John Paul Cook

John Paul Cook is a database and Azure specialist in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a registered nurse recently completed the education to become a psychiatric nurse practitioner. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog



Privacy Statement