I have a SQL database that stores dates as CURDATE() (2011-10-10) for example. I want to change the format of all the current records to (mm/dd/yyyy) how do I go about doing this?

Thanks a bunch,
X

What family of SQL server?

try

 FORMAT(column, 'MM-DD-YYYY')

should work on most

Originally Posted by chrishirst

What family of SQL server?

try

 FORMAT(column, 'MM-DD-YYYY')

should work on most

MySQL.

This is what I ended up doing (i'll defiantly log that though, as its much easier). Change the field datatype from DATE to varchar(11)

PHP Code:

<?php
    function formatdate( $s )
    {
    return date( "m/d/Y", strtotime( $s ) );
    }

        $query = 'SELECT id, date 
            FROM  `TBL` 
            WHERE DATE = DATE_FORMAT( DATE,  "%Y-%m-%d" )';
        $result = mysql_query($query) or die(mysql_error());
        while($data = mysql_fetch_object($result)) {
            $newdate = formatdate($data->date);
            $query = 'UPDATE TBL SET date = "'.$newdate.'" WHERE id = '.$data->id;
            $date = mysql_query($query) or die(mysql_error());
        }
        echo 'Updated Records';
    ?>

definitely not defiantly

One thing you should never, ever do is store dates as a string type, because one day it will come back on you.
that's definitely a programmer/wannabe DBA shortcut which rarely ends well.

Just gonna hop into this thread and reply to what Chris said above me ^. I always store my dates in an INT field (I store the Epoch timestamp), and the reason for that is entirely that I don't know exactly how the date field works in MySQL. Are there special limitations? The timestamp (PHP's time() function) works great for me, because you can format it however you please after it has been entered in the DB.

Originally Posted by chrishirst

definitely not defiantly

One thing you should never, ever do is store dates as a string type, because one day it will come back on you.
that's definitely a programmer/wannabe DBA shortcut which rarely ends well.

Mind explaining as to why? Knowledge is power, share?

Originally Posted by Physicsguy

Just gonna hop into this thread and reply to what Chris said above me ^. I always store my dates in an INT field (I store the Epoch timestamp), and the reason for that is entirely that I don't know exactly how the date field works in MySQL. Are there special limitations? The timestamp (PHP's time() function) works great for me, because you can format it however you please after it has been entered in the DB.

A datetime filed (at least in MySQL) is formatted as "YYYY-MM-DD HH:MM:SS". Using this format will allow the SQL engine to fully maximize date and time functions in a query.

To allow PHP to handle the datetime stamp, just use the strtotime() built-in function to convert it into a unix timestamp integer.

Because one day you may want to use the field in some expression like sorting/ordering according to that date or add/subtract it to some other date, and you won't be able to (at least not correctly) because those operations doesn't work for strings.

It's better to use the existing date types and then format them the way you want them, either with i.e. FORMAT as chris suggested, or after the DB query response with whatever language you are using.

Ahh, very good reasons, you guys!! I've always wondered the real use for strtotime, I thought it was for processing parsed dates back into their timestamps, but at the time I didn't know that was possible since some elements of the date could be missing (example: 'Friday, January, 2012' is missing the day, hour, minute, second, etc).

Thanks!

I wonder what the mad rush is going to be like when the world realises that all MySQL TIMESTAMP fields are going to fail?

reason:
Timestamp fields are stored as four bytes (32bits) and is the number of milliseconds since the "Unix Epoch" 00:00:00 01/01/1970 and the field will actually run out of space in 2037.

Will we be seeing the headlines about the "Twenty ThirtySeven" bug?

Originally Posted by chrishirst

Will we be seeing the headlines about the "Twenty ThirtySeven" bug?

Naw, programmers can just change their DATE fields to varchar(255) (255 so it lasts for a VERY long time)

No code being written today will still be in use in 2037... right?

Originally Posted by chrishirst

Will we be seeing the headlines about the "Twenty ThirtySeven" bug?

Surely we will. People will be stockpiling canned food to last a life time or two and hide in their nuclear shelters