SQL SERVER – Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

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.

Here is the complete error I received when I had used ISNULL function to int 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.

Before we fix the error, let us recreate this error.

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

Click to Download Scripts

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

15 thoughts on “SQL SERVER – Fix : Error: 217 Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.

  1. 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)

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

  2. 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.

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

  4. 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

  5. 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

  6. Hi below code is also the same result ..

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

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

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

    Thanks,
    Ragini

  9. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s