SQL SERVER – Fix : Implicit conversion from data type datetime to int is by using CONVERT function

Here. Question is. 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

Tag sql, sql error message