July 22, 2010 at 6:00 am
Consider the following script
CREATE TABLE Test_Table
(
Test_Column DATETIME
)
ALTER TABLE Test_Table ALTER COLUMN Test_Column INT
When i execute this script, i get an error saying Msg 257, Level 16, State 3, Line 6
Implicit conversion from data type datetime to int is not allowed. Use the CONVERT function to run this query.
Why am i getting this error even though it doesnot have any data in the table? And how do we re-write this using a CONVERT function?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 22, 2010 at 6:55 am
There is no implicit conversion from DATETIME to INT. If your table has no data, drop it and recreate it. Otherwise, what you need to do is create the new structure on the side, drop all constraints from the old table, rename the old table, name the new table to the old table, migrate the data by hand, recreate the constraints on the new table, drop the old table.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2010 at 7:04 am
Grant Fritchey (7/22/2010)
There is no implicit conversion from DATETIME to INT. If your table has no data, drop it and recreate it. Otherwise, what you need to do is create the new structure on the side, drop all constraints from the old table, rename the old table, name the new table to the old table, migrate the data by hand, recreate the constraints on the new table, drop the old table.
Actually, in my real table i had the values as NULL for the particular column and it was giving the same error. Then i changed the data type from DATETIME to VARCHAR and then from VARCHAR to INT and it worked. But i was just curious to know if there is some way to re-write the statement using CONVERT function as the error suggests.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 23, 2010 at 1:48 am
i also tried it . datatime --> varchar ---> int . it worked because sql server allows varchar to int conversion only incase when column contains null values but it gives error when column has some values
try this
CREATE TABLE Test_Table
(
Test_Column DATETIME
)
insert into Test_Table select getdate()
ALTER TABLE Test_Table ALTER COLUMN Test_Column varchar(200)
ALTER TABLE Test_Table ALTER COLUMN Test_Column int
First ALTER will wok but second gives below error
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Jul 23 2010 3:44AM' to data type int.
The statement has been terminated.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 5, 2015 at 10:29 am
Is there no straight forward answer?
why we have to change to varchar then to INT?
June 5, 2015 at 10:37 am
My question, why are you converting a datetime column to an int column? If it is a date/time value use datetime data types.
June 5, 2015 at 10:54 am
My SSIS package has a Months column with 2 digit values but my table has Months column in Date datatype I want to convert
June 5, 2015 at 10:58 am
krismaly (6/5/2015)
My SSIS package has a Months column with 2 digit values but my table has Months column in Date datatype I want to convert
That's the wrong data type. If you want to store dates, use one of the datetime data types. If you want to store a quantity of months, use a numeric data type.
June 5, 2015 at 11:00 am
Thanks
June 5, 2015 at 11:15 am
krismaly (6/5/2015)
Is there no straight forward answer?why we have to change to varchar then to INT?
What are you trying to do?
If the date is 01/01/2014, are you trying to store 01012014?
If so, and int wont work because of the leading 0
How about:
ALTER TABLE MyTable DROP COLUMN DateColumn
ALTER TABLE MyTable ADD DateColumn int
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
June 5, 2015 at 11:19 am
I used following statements and it worked fine
ALTER TABLE Test_Table ALTER COLUMN Test_Column varchar(200)
ALTER TABLE Test_Table ALTER COLUMN Test_Column int
But I am looking for one statement not two
June 5, 2015 at 11:22 am
[font="Arial Black"]Hey, why are we trying to change a column declared as a DATETIME column to an INT? If it is a Date/Time value store it as such.[/font]
Sorry for yelling but I felt my question was being ignored.
June 5, 2015 at 11:31 am
:-):-)
You are fine
You are helping
:-):-)
June 5, 2015 at 11:34 am
krismaly (6/5/2015)
:-):-)You are fine
You are helping
:-):-)
I hope you aren't changing your datetime to an integer so you can store your dates as MMDDYYYY instead of strong them as dates.
I don't see how ignoring the most basic question helps. So why are you changing it from a datetime to an integer?
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply