Click here to Skip to main content
15,795,793 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I am getting this error while converting to int datatype

I am using query this to convert


but getting this error .. ?

Error:\'Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.'

Use DATEDIFF function like this:
SELECT CAST(41657 AS datetime)
SELECT DATEDIFF(DAY, 0, CAST(41657 AS datetime))
Share this answer
Christian Graus 20-Jan-14 22:55pm    
OK, that makes zero sense. You're not using today's date anywhere in there. Nor do you end up with yyyymmdd as the final result.
Sergey Vaselenko 21-Jan-14 5:35am    
The first line shows how to convert an integer value to a datetime value.
Further the datetime value can be converted to a varchar, for example.

The second line shows how to convert a datetime value to an integer one.
The function result is 41657 that confirms that conversions work in both ways correctly.
Christian Graus 21-Jan-14 13:35pm    
But if you read my answer, the code he posted was correct, and working, and it turned a datetime in to an int in the format yyyymmdd. Yours turns a date time in to an int that, to a human reader, is arbitrary
Sergey Vaselenko 21-Jan-14 15:17pm    
You are right. DATEDIFF is just another solution.
You are converting a date to a string, then trying to cast to int. That makes zero sense.

convert(int, getdate())

will do it, if it's possible.

What do you expect to get ? How do you expect the int to relate back to the date ? Are you sure you don't want the day, month or year functions ?

select convert(int, getdate()) returns 41658 for me, every time, so it seems to ignore the time, however it's working.

funny enough, select CAST(CONVERT(VARCHAR(8),GETDATE(),112)AS INT) works in SS2008.

Actually select CONVERT(VARCHAR(8),GETDATE(),112) returns yyyymmdd, so in fact that does make sense, although I'm not sure why you want it as an int.

so, it does work. You marked your question as four versions of SQL Server. I can tell you it works in SS2008 R2. Try selecting just the string part and see if that works.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900