SQL SERVER – Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed.

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:

Solarwinds

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

SQL SERVER - Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed. error-257

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.

SQL SERVER - Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed. error-257-1

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)

Solarwinds
, ,
Previous Post
SQL SERVER – The procedure attempted to return a status of NULL, which is not allowed. A status of 0 will be returned instead.
Next Post
SQLAuthority News – HP Project Moonshot Interchangeable and Interlockable Servers with Elastically Scale NuoDB

Related Posts

16 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)

    Reply
    • Hi Kdoods,

      I am aware of that syntax. However, there are many readers of this blogs are still not migrated to SQL Server 2012 and they get error when they run this. This is the reason I have switched back to older method till the entire world is on SQL Server 2012.

      Reply
  • 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.

    Reply
  • pareshpatelsql
    April 9, 2013 11:09 am

    we can use like this also :
    SELECT ItemID, ISNULL(convert(varchar(26),IsAvailable), GETDATE()) AvailableNow
    FROM #TestTable

    Reply
  • SELECT ItemID, COALESCE(IsAvailable,GETDATE()) AvailableNow
    FROM #TestTable

    Reply
  • Here is my idea…
    SELECT ItemID, ISNULL(cast(IsAvailable as varchar(26)), convert(varchar,GETDATE(),121)) AvailableNow
    FROM #TestTable

    Reply
  • 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

    Reply
  • 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

    Reply
  • Hi below code is also the same result ..

    select ItemID,ISNULL(convert(varchar(26),IsAvailable),GETDATE())
    from #TestTable

    Reply
  • 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

    Reply
  • Hello Pinal,
    How about this?:
    CASE WHEN UpLoadedDateTime IS NULL THEN
    CONVERT(VARCHAR(10),”) ELSE
    CONVERT(VARCHAR(10),UploadedDateTime)

    Thanks,
    Ragini

    Reply
  • 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

    Reply
  • 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 ?

    Reply

Leave a Reply

Menu