formatting a date field in mysql

Question

Hi all,

I have a birthdate field stored in a table in the database.At present it displays the values as 2005-10-12 but i wanted to display as 10-12-2005.I have tried the following query but it doesnt display the field at all in PHP.Any suggestions will be helpful.By the way this query when executed directly on the database it is dispalyed but not when in php.Thanks in advance.

     SELECT DATE_FORMAT(birthdate,'%m-%d-%Y'),name FROM persons ORDER BY name DESC

Answer 1

It should display properly. If you are trying to get the field in PHP for example, it doesn't appear as birthdate, but rather as it's written: DATE_FORMAT(birthdate,'%m-%d-%Y'). That's probably why it is not in $row['birthdate'] but rather in $row["DATE_FORMAT(birthdate,'%m-%d-%Y')"], if at all.

To get it as birthdate, use an alias to the field with as keyword:

SELECT DATE_FORMAT(birthdate,'%m-%d-%Y') as birthdate, name FROM persons ORDER BY name DESC

Now it fill be found in $row['birthdate'].

A more flexible way is to select the raw date (preferably in UNIX_TIMESTAMP format) and format the date in your programming language. In PHP, you could do something like this:

$query = "SELECT UNIX_TIMESTAMP(birthdate) as birthdate, name FROM persons ORDER BY name DESC";
$resource = mysql_query($query);

while($row = mysql_fetch_assoc($resource)) {
    echo date('m-d-Y', $row['birthdate'])." ".$row['name']."<br />";
}

Answer 2

Here is a good site for formatting dates using the mysql date_format function http://www.mysqlformatdate.com