• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

Amending php myadmin sql database

Hello,

I need some help/advice on updating my database.

Stupidly i set my Date column structure to varchar(250) then populated the database with 226048 rows of code  :(

is there away i can convert the current date (displayed like 18/10/2014) and then convert the column to be Date ??
0
runnerjp2005
Asked:
runnerjp2005
  • 3
  • 3
1 Solution
 
John EastonDirectorCommented:
You should be able to create a new date column, and then update it with the date in the varchar column.  After checking the data you can then drop the varchar column.
0
 
runnerjp2005Author Commented:
Thats a good idea!!!

what sql statement would i need to update from `date` to date2?
0
 
John EastonDirectorCommented:
I've only done this in MS SQL, but MySQL should be able to do the same.  From memory (assuming you are editing every row in the table) all you should need to do is (replacing the field and table name accordingly):

UPDATE myTable SET newDate = oldDate

Open in new window


Needless to say, if this is a live system please backup first.  Any SQL that can update large volumes of data could cause data loss if it fails, has a typo etc.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
runnerjp2005Author Commented:
Yer currently doing this on a copy of the table

UPDATE `horses2` SET `Date2` = `Date`

tried the above and it updates 0 rows :S
0
 
John EastonDirectorCommented:
Just created a table to test this, and got the same result.  You need to add the Str_to_date function as shown below:
UPDATE `datetest` SET `newdate`=STR_TO_DATE(`olddate`,'%d/%m/%Y')

Open in new window

This assumes all the dates are entered in UK format (i.e. 20/01/2014).  If the format is different you should edit the format.  Guidance is available here:  Date Format
0
 
runnerjp2005Author Commented:
Ta Bud saved me ALOT of time there :)
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now