Mysql updating dates in a table

[JD Austin]

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 at twingeckos.com
480.288.8195x201
http://www.twingeckos.com


Pablo Picasso<http://www.brainyquote.com/quotes/authors/p/pablo_picasso.html>
- "Computers are useless. They can only give you answers."

On Tue, Aug 4, 2009 at 3:44 PM, Craig White <craigwhite at 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 at lists.plug.phoenix.az.us
> To subscribe, unsubscribe, or to change your mail settings:
> http://lists.PLUG.phoenix.az.us/mailman/listinfo/plug-discuss
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.PLUG.phoenix.az.us/pipermail/plug-discuss/attachments/20090804/742e9f39/attachment.htm