473,324 Members | 2,531 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

alter table query

hi all,

i have a table that has a column date varchar(20) and now i learned some
more about mysql I want to make it date date but if I do an alter table on
it what will it do to my existing data which currently is in the format 11
June 2004 (but in a varchar type).

many thanks!

F

Jul 20 '05 #1
1 2931
FRED wrote:
i have a table that has a column date varchar(20) and now i learned some
more about mysql I want to make it date date but if I do an alter table on
it what will it do to my existing data which currently is in the format 11
June 2004 (but in a varchar type).


I suggest that you use:

Warning, I wrote the next queries from my memory, I haven't tested them
so they might contain syntax errors or work incorrectly, but you should
get the idea of what you need to do:

alter table tablename add temp_column date;
update tablename set temp_column = xxxxx;
alter table tablename change old_date_column new_column_name date;
update tablename set new_column_name = temp_column;
alter table tablename drop temp_column;

xxxx depends on what version of MySQL you have

If you have MySQL 4.1.1. you can use STR_TO_DATE() function.
http://dev.mysql.com/doc/mysql/en/Da...functions.html

If you don't, then you have to parse it using string functions, which
will do a little more job. ( You would need to make your currect date in
format yyyy-mm-dd and especially the month name will give us some extra
work. )

http://dev.mysql.com/doc/mysql/en/String_functions.html
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Heist | last post by:
Hi, I just want to know to turn this: CREATE TABLE . ( NOT NULL , (50) COLLATE French_CI_AS NULL , NOT NULL , (50) COLLATE French_CI_AS NOT NULL , NULL , NULL ) ON into this:
1
by: Bruce | last post by:
Hi, I want to change the datatype of an existing column from char to varbinary. When I run the "Alter Table" statement, I get the following error message - Disallowed implicit conversion...
7
by: Jon Combe | last post by:
I have created the following test SQL code to illustrate a real problem I have with some SQL code. CREATE TABLE JCTable ( CustomerName varchar(50) ) ALTER TABLE JCTable ADD CustomerNo int...
1
by: boonkit | last post by:
I try to get better performance by implementing this: http://dev.mysql.com/doc/mysql/en/alter-table.html "ORDER BY allows you to create the new table with the rows in a specific order. Note...
7
by: pb648174 | last post by:
I am trying to add a column to a temp table and then immeditaely query against that new column. If I do this in Query Analyzer it works fine as long as there is a go in between, but I can't use a...
0
by: Gianfranco | last post by:
Hi, I'm not familiar at access and at VBA either, so I need some help or at least some hint please. I have table "availabledrivers", coming from a query to the table "drivers". Then I have a table...
2
by: Jeff_in_MD | last post by:
Hi, I'm trying to add a column to a table, then update that column with a query. This is all within a single batch. Sqlcmd gives me an error on the update, saying "invalid column xxx", because...
4
by: JenavaS | last post by:
I am trying to create a macro to change a data type in a column. The table was created using a "make-table" query, and the column(s) I want to modify were created using a calculation in my query. ...
7
by: quincy451 | last post by:
drop table . CREATE TABLE . ( NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , NULL , NULL , (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , (16) COLLATE
1
by: Chris | last post by:
Hi, I am trying to run the following SQL through the DB2 command line for version 9.1.0: alter table SCHEMA.TABLE1 drop column A; alter table SCHEMA.TABLE2 alter column B set data type...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.