Implicit Conversion Fail

Implicit

Every now and again, you may run into an error about implicit conversions in your queries. Implicit conversions are not uncommon in the computing world and can be viewed as kind of a fail-safe for when we don’t quite follow decent practices when designing the database or when writing queries or both.

Despite this little fail-safe, there are times when a nasty little error will pop up and cause a bit of consternation.

Implicit conversion from data type %ls to %ls is not allowed. Use the CONVERT function to run this query.

What Went Wrong

Unlike many other errors in SQL Server, this error message makes some sense. The major components of what is wrong are present and you are given a decent idea of what the conversion attempt is that failed. When this particular error happens, you can bet that there are issues with some TSQL code somewhere for sure. In addition, you can bet there is likely a problem with the database design as well. Yay! More work for your back burner.

First, this error comes with an error id of 257 and we can see the message text via the following query. This id is important for when we want to monitor for this problem in the future.

SELECT *
FROM sys.messages m
WHERE m.message_id = 257
AND m.language_id = 1033;

Let’s see how we can recreate this problem.

DECLARE @BeginDate DATE = '2019-01-01',
        @EndDate DATE = '2019-12-31';
/* quick and easy method to create a test table with millions of rows if needed */SELECT TOP 100
       ImplicitID = IDENTITY(INT, 1, 1),
       ImplicitInt = ABS(CHECKSUM(NEWID())) % 50000 + 1,
       ImplicitDate = DATEADD(DAY, RAND(CHECKSUM(NEWID())) * (1 + DATEDIFF(DAY, @EndDate, @BeginDate)), @EndDate)
INTO #ImplicitTest
FROM master.dbo.syscolumns t1,
     master.dbo.syscolumns t2;
ALTER TABLE #ImplicitTest ADD PRIMARY KEY CLUSTERED (ImplicitID);
SELECT *
FROM #ImplicitTest dq
WHERE ISNULL(dq.ImplicitID, GETDATE()) = '5/17/2020';
DROP TABLE #ImplicitTest;

Which will produce the following.

This is a prime example of a bad query producing an error. Obviously, I am using the wrong data type to try and query the temp table. The ImplicitID column is an integer and I am trying to query it using a date. The quick fix, would be to query the appropriate date column if I must use a date in my query, or i can use an integer to query the ImplicitID column.

SELECT *
FROM #ImplicitTest dq
WHERE dq.ImplicitDate > '5/17/2019';

After this minor tweak, now the query will work and I start to see results. Given the random nature of the data in this query, the results will vary from batch to batch.

Wrapping it Up

Implicit conversions are a fail-safe for when bad design or code (or both) crops up in your environment. Sometimes, this fail-safe is inadequate and needs further assistance. Sometimes, that may be an explicit conversion and sometimes that means an appropriate rewrite of the query to use the appropriate columns and data types in the queries. This was an introductory article into the world of implicit conversions. There will be a follow-up or two about implicit conversions and monitoring for them. In preparation for the upcoming articles, I recommend reading a bit on Extended Events to get up to date. For other basics related articles, feel free to read here.