Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 3rd, 2009, 10:29 AM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default Implicit conversion error on Alter Column?

I have code that generates the following SQL statement:

ALTER TABLE SurveyResults598 ALTER COLUMN Q13810 REAL

Q13810 is currently defined as a DATETIME column. I accept that data is likely to be lost or rendered useless but I still need to change the column from a DATETIME type to a REAL type. When I run it, I get this error:

Implicit conversion from data type datetime to real is not allowed. Use the CONVERT function to run this query.

Note that I am able to successfully convert REAL to DATETIME without causing this error. And I can go both directions between VARCHAR and REAL. Only DATETIME to REAL is causing this.

So, how do I specify a conversion such as this error suggests to change a column DATA TYPE from DATETIME to REAL?
Reply With Quote
  #2 (permalink)  
Old November 3rd, 2009, 01:14 PM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Since your data will become useless, isn't easier (and probably cheaper) to just delete that collumn and create a new one????
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
Reply With Quote
  #3 (permalink)  
Old November 3rd, 2009, 01:50 PM
Friend of Wrox
Points: 2,876, Level: 22
Points: 2,876, Level: 22 Points: 2,876, Level: 22 Points: 2,876, Level: 22
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Denver, CO, USA.
Posts: 428
Thanks: 57
Thanked 2 Times in 2 Posts
Default

Thanks, but a simple Alter Column works 99% of the time. Writing work-around code to drop and then rebuild columns for this one circumstance doesn't sound easier to me. Of course, if there's no other option then I'll have to go that route, but if it's possible to add a "Convert" clause or whatever to the existing Alter Column so that SQL server doesn't puke as it does currently, I'd prefer to go that route. I just do not know enough about SQL to know how to correct whatever is causing this error the "SQL way."
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
implicit conversion seymour_glass C# 7 November 4th, 2007 05:42 PM
Alter Column SQLScott SQL Server 2005 5 January 5th, 2007 11:53 AM
Alter Column - Default Value sasidhar79 SQL Server 2000 2 January 18th, 2005 07:30 AM
Error, Strict On disallows implicit conversions f vbGaz VB.NET 2002/2003 Basics 0 January 3rd, 2005 04:27 AM
Alter Default on a Column Gert SQL Server 2000 4 November 28th, 2003 08:58 AM



All times are GMT -4. The time now is 11:11 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.