The database requires the date to be entered in the format 2008-04-29 if the field datatype is date.

Can PHP be used to format this date to make it 29-04-2008 or 29th April 2008?

Or can you specify the format to store the date in the databse?

I don't know about MySQL formatting, but I can tell you how this works in SQL Server, and assume that it's fairly similar.

The date isn't actually stored in any particular format. It's stored as a series of byte values. I think it's about 1.5 bytes for the month and day, and 2.5 for the year, or 4 in all. (We have small and full date time types.) How you express these (MM-DD-YYYY or DD-MM-YYYY or YYYY-DD-MM or MM-DD-YY) actually isn't connected to how they're stored on disc. And for that reason, we're actually able to input them (using insert commands) in any particular format, so long as the server can tell what's what.

To get them formatted a particular way, our choices are limited. There are a few pre set ones that we can use the Convert function to get. Or we can use DatePart functions to extract the month, day, and year, then use string concatenation to put them back together. But for the most part, it doens't matter how they're formatted where SQL runs. Formatting is a function of consuming data, not processing it. So our ASP.NET code might query the database and fetch a list of values, including a date, and then format it in such and such a way while writing HTML all around it.

Here is a simple function I wrote to convert MySQL dates to any format needed:

PHP Code:

function convert_date($date, $format) {
    $date = strtotime($date);
    $date = date($format, $date);
    return $date;
} 


The $date parameter is the MySQL date, and $format is the desired output, formatted according the PHP's built in date() function, which you can read about here: http://php.net/date

To use this function for the second format you specified, you would do something like this:

PHP Code:

echo convert_date($mysqlDate, "dS F Y"); 


Thanks for your replies, I managed to format the date using MySQL

PHP Code:

SELECT date_FORMAT(A.date, '%M %e, %Y') as newDate, 


which prints out the date as:

April 29, 2008

Thanks,

Interesting. I didn't know about that one.