[RESOLVED] Date Format from database

[Lemonboi]

I want to format the date I'm retrieving from the database to another format but can't seem able to do it, I did a search and found a possiblity but it dosen't seem to work for me.

I'm using MySQL database and PHP 4

The current database date is in yyyy-mm-dd format and I either want to rearrange it to dd-mm-yyyy or into 'ddth January, yyyy'

i orignally tried this
$query = "SELECT column1, DATE_FORMAT(date, '%M %e, %Y') AS date FROM table WHERE id='$id'";

[pkghost]

have you thought about using timestamps instead of hard-formatted dates?

if you use timstamps, you can then use the php script to format the date however you want.

i find them extremely useful. check it out at http://us3.php.net/manual/en/function.mktime.php

[Lemonboi]

I've got my database field in DATE format and want to query it and trasform the timestamp into a format that I wish to display.

So what you're syaing is just extract the time stamp and then use php script to format it?

[planetsim]

Although that method is possible your wasting a lot of chance to utilize something that is built into MySQL and probably if tested a tad faster than PHP formatting it.

SELECT DATE_FORMAT( '2005-05-20', ' %D %M %Y' ) AS mydate

Seemed to do what you wanted.

[Lemonboi]

So what "type" should the date field be in the database table?

[thorpe]

date

[Lemonboi]

I seem to have sorted it out now thanks for the help all