echo Formatted Date from DATETIME field

[bhattvk]

I store date() in postdate field (DATETIME) when a row is insterted. When showing it with echo, how do I format it as mm/dd/yy instead of "yyyy-mm-dd hh:mm:ss" format?

Any php function for that?

Pls help.

[mrmufin]

There are a whole bunch of date-formatting utilities available in MySQL:

Assuming your datefield was named 'last_modified', you could format it's return value from the database using the DATE_FORMAT() function like this:

SELECT DATE_FORMAT(last_modified,"%m/%d/%Y") FROM table

Or, if you prefer to format the query result using PHP, you can select the temporal data as a UNIX timestamp and use PHP to manipulate the returned integer value:

SELECT FROM_UNIXTIME(last_modified) FROM table

then format the value using the date() function:

date("m/d/y",$last_modified)

Have fun...

[lividsp]

I am having the same problem. I am trying to get mysql to spit out the date in mm-dd-yyyy format and the date_format function doesn't seem to work.

I tried using the date_format as both you and my book suggest, but it doesn't seem to work. the double quotes seem to cause an error. ... and when I switch to single quotes, the script runs , but the date doesn't change.

I have tried it with and without slashes, every different way I can imagine. any ideas?

[hand]

SELECT DATE_FORMAT(datefieldintable,"%m-%d-%Y") as myDate FROM table