0
I’m trying to change the type of a column of my table, which is empty, from smalldatetime to integer.
Error:
Implicit conversion of data type from smalldatetime to int is not permitted. To execute the query, use the CONVERT function.
I’m trying this way :
ALTER TABLE Table_Name
ALTER COLUMN Column_Name INT NOT NULL
NOTE: When I try to convert to another type, example: VARCHAR(20), it works.
What am I doing wrong ?
2
Because it is not possible to convert a type smalldatetime for int. Imagine telling SQL "convert 01/01/2019 to an integer," how to do that? How should it convert? Divide 1 by 1 and then by 2019? He doesn’t know exactly what to do in this conversion.
To varchar works because it is a text, so it is possible to store the data as it is, with everything that there is besides numbers (bars, two-points, etc).
What can be done is, change to varchar, make a update format correctly only with numbers, and then change again to int
Assuming your table is called "Test" and the column smaldatetime if you call yourself "field", you could do so:
datediff based on this other question: Convert smalldatetime to Unix timestamp.
alter table teste
add campo_int int;
update teste
set campo_int = datediff(ss, '19700101', campo);
alter table teste
drop column campo;
You can see an example working here: sqlfiddle
Works this way even with the empty table ?
even if the table is empty this change is not allowed, the same error will happen
Browser other questions tagged sql-server type-conversion
You are not signed in. Login or sign up in order to post.
if you have any questions about how to convert, tell me what format the date will be in full and I can help with sql
– Ricardo Pontual
@Ricardo Pontual the date will be in the format of Unix Timestamp. I am converting everything to Varchar and then to whole, if there is any way to make this conversion faster thank you.
– Levi