http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
You can use Str_to_date to convert it to a date.
Date/timestamp data should be one of the date types (Datetime, date, timestamp).
I'd do it like this (all sql and won't lose data):
  1. Add a column to the table that is date/datetime/timestamp (in your case date looks right)
  2. update the new column using the str_to_date function
  3. Check for rows that have nulls in that column - fix them
  4. set the existing column value to NULL
  5. change the column to a date using alter table.
  6. update the column to be whatever you called the new column you added.
  7. Drop the new column that you added.
  8. add not null constraint to your original date column
--
JD Austin
Twin Geckos Technology Services LLC
jd@twingeckos.com
480.288.8195x201
http://www.twingeckos.com


Pablo Picasso  - "Computers are useless. They can only give you answers."

On Tue, Aug 4, 2009 at 3:44 PM, Craig White <craigwhite@azapple.com> wrote:
I need to update almost all (but not quite all) records in a data table
because the dates were imported like '08/04/09' instead of '2009-08-04'

I am looking at the mysql reference manual but it doesn't appear that it
would be easy to script a line by line 'update' from a csv file.

Now I still have the csv files (and a copy with the dates fixed) and I
suppose I could 'find', 'delete' and then 'import' again but there are
relational records so mysql would probably prevent me from doing that
unless I suspend all relational checks when I delete those records prior
to reimporting them with the fixed dates.

What's the best way to accomplish this?

Craig


--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

---------------------------------------------------
PLUG-discuss mailing list - PLUG-discuss@lists.plug.phoenix.az.us
To subscribe, unsubscribe, or to change your mail settings:
http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss