[GLLUG] MySQL Help

[Marshal Newrock]

On Thursday 30 March 2006 21:51, Mel Wade wrote:
> I've got a problem with a MySQL database and need some help.
>
> The field 'birthday' is varchar and a recent import of users placed
> birthdates in the following format.
>
> 1970-12-31
>
> There are supposed to be
>
> 12-31-1970
>
> I need to be able to fix this, but not change the ones that are correct.
>
> I could so this in switch easily in ColdFusion and MSSQL (my home turf),
> but I'm not sure about PHP and MySQL.
>
> Can anyone help me with a quick fix for this?

Don't try to change the date format in the database.  year-month-day is an 
ISO standard, is easily sortable as a text sort, and is unambiguous (ie, 
3-5-1970 might be March 5 or May 3).  If you alter it in the database, you 
won't be able to get dates between a certain range or properly sorted.

Since the field is a varchar and not a date field, I would change it in PHP, 
using strtotime() and date().  Alternatively, if you can alter the field to 
be a date field (always back up your database first), you can change your 
select from mysql with DATE_FORMAT() (see 
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html).

-- 
Marshal Newrock
Ideal Solution, LLC - http://www.idealso.com