THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - 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 @xDATETIME DATETIME;

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a Technology Solutions Professional for Microsoft's data platform and works out of Microsoft's Houston office. Prior to joining Microsoft, he was a Microsoft SQL Server MVP. He is experienced in Microsoft 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 who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at safety net clinics. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement