Just a day before I was working on some query and faced this error. As soon as I receive this, I realized that what I had done wrong and what I needed to fix. The suggestion demonstrated in the error message is to the point and accurate. Let us learn how to fix error 217 for Implicit conversion.
Here is the complete error I received when I had used an ISNULL function to its value and in case of the null value, I wanted to display current date time.
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
-- Create table CREATE TABLE #TestTable (ItemID INT, IsAvailable INT) -- Insert into table INSERT INTO #TestTable (ItemID, IsAvailable) SELECT 1, NULL UNION ALL SELECT 2, 1 UNION ALL SELECT 3, 1 UNION ALL SELECT 4, NULL UNION ALL SELECT 5, 1 GO
Now let us run following T-SQL script which should throw an error for us.
-- Following will throw error SELECT ItemID, ISNULL(IsAvailable, GETDATE()) AvailableNow FROM #TestTable GO
Above T-SQL script will give following error:
Msg 257, Level 16, State 3, Line 2
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Now we can overcome above error by using following three workarounds.
Solution / Workaround:
-- Fix 1 SELECT ItemID, ISNULL(CONVERT(DATETIME, IsAvailable), GETDATE()) AvailableNow FROM #TestTable GO -- Fix 2 SELECT ItemID, COALESCE(IsAvailable, GETDATE()) AvailableNow FROM #TestTable GO -- Fix 3 SELECT ItemID, CASE WHEN IsAvailable IS NULL THEN GETDATE() ELSE IsAvailable END AvailableNow FROM #TestTable GO
Now above three examples will fix the problem. However, there is one small problem here. When I use either ISNULL or COALSECE the value IsAvailable which is INT now is converted to the datetime and display value of year 1900.
Now you can see even though we found workaround it has small issue – this is better than the error which we were getting before, but still not accurate.
Here is the one more solution which will overcome the incorrect data issue described above.
-- Fix 4 SELECT ItemID, CASE WHEN IsAvailable IS NULL THEN CONVERT(VARCHAR(26), GETDATE()) ELSE CONVERT(VARCHAR(26), IsAvailable) END AvailableNow FROM #TestTable GO
Here is the screenshot of the resultset.
Well, let me know if there is any better solution and I will gladly include it here with due credit.
Run following command to clean up.
-- clean up DROP TABLE #TestTable GO
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
17 Comments. Leave new
Hello Dave
I appeciate your articles. Here is a syntax to save you lots of typing when entering several values into a table manually, as when setting up your examples
INSERT INTO #TestTable (ItemID, IsAvailable) VALUES
(1, NULL),
(2, 1),
(3, 1),
(4, NULL),
(5, 1)
Fix 4 is similar as Fix 3 , Only Convert to varchar is included.
We can do similar conversion in Fix 1 & Fix 2
SELECT ItemID, ISNULL(CONVERT(VARCHAR(26), IsAvailable), CONVERT(VARCHAR(26),GETDATE())) AvailableNow
FROM #TestTable
GO
SELECT ItemID, COALESCE(CONVERT(VARCHAR(26), IsAvailable), CONVERT(VARCHAR(26),GETDATE())) AvailableNow
FROM #TestTable
GO
And, we can write all this query using CAST statement also.
we can use like this also :
SELECT ItemID, ISNULL(convert(varchar(26),IsAvailable), GETDATE()) AvailableNow
FROM #TestTable
SELECT ItemID, COALESCE(IsAvailable,GETDATE()) AvailableNow
FROM #TestTable
Here is my idea…
SELECT ItemID, ISNULL(cast(IsAvailable as varchar(26)), convert(varchar,GETDATE(),121)) AvailableNow
FROM #TestTable
Hi Pinal,
I think we can also convert the getdate function into int. Below is the query:
Select ItemID, ISNULL(IsAvailable,Convert(int,convert(varchar(20),Getdate(),112))) as AvailableNow From #TestTable
Hi i think we can also use coalesce function along with convert function ..
select ItemID,coalesce(convert(varchar(26),IsAvailable) ,convert(varchar(26),getdate())) as AvailableNow
from #TestTable
Hi below code is also the same result ..
select ItemID,ISNULL(convert(varchar(26),IsAvailable),GETDATE())
from #TestTable
sir my problem is that i have column which has datatype nvarchar(50) and now i have to convert that data into datetime datatype how to do it ….plz help
Hello Pinal,
How about this?:
CASE WHEN UpLoadedDateTime IS NULL THEN
CONVERT(VARCHAR(10),”) ELSE
CONVERT(VARCHAR(10),UploadedDateTime)
Thanks,
Ragini
Since I like using standard SQL as possible. Another approach is…
SELECT ItemID, COALESCE(CAST(IsAvailable AS VARCHAR), CAST(GETDATE()AS VARCHAR)) AvailableNow
FROM #TestTable
GO
implicit conversion from data type varbinary to date is not allowed
I am getting in liquibase change set can you please let me know how it can resolve ?
Well i know that this is an old post, but maybe i have a bit of luck.
I’m facing an issue when trying to keep a specific format to date. My query is a WSUS SQL query for Last Reported Status
If i use with both converted as VARCHAR i get the result as “Aug 7 2022 8:21PM” if date exists and “Not Reported” if is NULL
CASE WHEN ct.LastReportedStatusTime IS NULL THEN CONVERT(VARCHAR(26),’Not Reported’) ELSE CONVERT (VARCHAR(26), ct.LastReportedStatusTime) END LastReportLocalTime
But my desired time format “datetime2(0)” should be 2022-07-22 20:21:34.
If i first convert to VARCHAR and after ELSE to datetim2 i get NULL value.
Is there any way i can actually Keep the date format if not Null and if null to input a string like “Not Reported Yet” ?