Solved

MySQL Alter Table

Posted on 2013-11-08
5
458 Views
Last Modified: 2013-11-10
I have a MySQL table i inherited, there is a column with a datatype of varchar(50) all the values are dates.

When I try to change the data type to Date and apply I am getting the following message

ERROR 1292: Incorrect date value: '12/01/1943' for column 'DOB' at row 1

I have another column in the table set as date and it returns in this format
2011-07-20

This looks like the problem, any quick fix to this?
0
Comment
Question by:Brogrim
  • 3
  • 2
5 Comments
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
You'll need to make sure all the VarChar dates are in a format that will easily convert to DateTime format - YYYY-MM-DD

If all the VarChar dates are in a particular format (such as DD/MM/YYYY), then you can write a query to change that around, and update the field so it's in the correct format.

Then you'd be able to change your Data Type easily
0
 
LVL 42

Accepted Solution

by:
Chris Stanyon earned 500 total points
Comment Utility
You'll need to run a query like this before altering your table:

UPDATE yourTable SET DOB = DATE_FORMAT(STR_TO_DATE(DOB, '%d/%m/%Y'), '%Y-%m-%d');

Open in new window

This will convert the VarChar date from dd/mm/yyyy format to the yyyy-mm-dd format. Once that's done, you can alter your table and change the column datatype to date.

If you dates are stored in mm/dd/yyyy format (difficult to tell from 12/01/1943!) then just change the STR_TO_DATE function to this:

STR_TO_DATE(DOB, '%m/%d/%Y')
0
 

Author Comment

by:Brogrim
Comment Utility
Thanks for the suggestion and no doubt it would work if all the dates were in the format dd-mm-yy, unfortunately the column been varchar (50) there were multiple instances like

002/1/1929
1/122/201
 and so on

Below is the message I got back

Error Code: 1292. Truncated incorrect date value: '6/6/12922'


What I have ended up doing is writing a statement

select id, dob, CONCAT(right(DOB,4),+-+ MID(DOB, 4,2),+-+ left(DOB,2)) as NEWDOB from tbl where dob is not null

Open in new window


I started off with 41,000 records and ended up with 40,200 as alot could'nt be salvaged

I did have to run the query multiple times before I could then create another table, I then added a new column DOB1 and updated DOB1 through the ID join on both tables.

Long way around it but its working.
0
 
LVL 42

Expert Comment

by:Chris Stanyon
Comment Utility
Yeah - no easy way to do it if your varchar column contains garbage - bad database design from the off, I think :(

Pleased you got it working
0
 

Author Closing Comment

by:Brogrim
Comment Utility
Thanks Chris, the comment you made was correct and would have worked but for the initial bad design.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
This video discusses moving either the default database or any database to a new volume.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now