How to change a field type in mysql and keep its value?

Question

0.00/5 (No votes)

See more:

I've a table in MySQL database which contains a field of type string (varchar(20)). Its content is of type time, like 23:30, 14:00, etc. Although its length is 20, only 5 characters are required. Anyway, I prefer to change it to a field of type int with content 0 to 47 to indicate 00:00 to 23:30. But I need to keep contents of customers' databases. I may simply run:

SQL

alter table Schedules modify Start tinyint(1);

But this resets contents of the field in rows. I need a MySQL script to do it without clearing the contents. Maybe I've to add an intermediate column.

What I have tried:

Don't know how to keep the field contents in rows. This is a question I've no clue about.

Posted 28-Jun-17 23:03pm

ilostmyid2

Updated 29-Jun-17 8:05am

Add a Solution

Comments

Mehdi Gholam 29-Jun-17 5:20am

First the data types should be compatible, otherwise you should convert them first then change the data type.

ilostmyid2 29-Jun-17 8:39am

how?

Andy Lanng 29-Jun-17 10:18am

Update the column contents:
Update "table"
Set "column" = "0" where "column"="00:00"
Best would be to use case tho

ilostmyid2 1-Jul-17 6:43am

thank u

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

W∴ Balboos, *GHB* · Accepted Answer · 2017-06-29T08:05:00

The values in time format don't have any numerical representation. Thus, if you convert the column's type it will wipe clean whatever it can't convert. By comparison, a table of char types, that are all, for example, just plain numbers, will convert when the column's changed. It's a matter of 'can it be represented in the new column type?'.

Best solution is to create an new column of type int, update your table so you convert your times to int format, and then drop the time column.

Alternative: use a computed column value in the new column and handle the work in a function so that you can enter ab:cd format and it will be converted in the alternate column automatically.