formatting datetime

[james438]

After retrieving the date from the datetime field of my MySQL database I am trying to figure out how to format it into say the date("n/j/Y",$date); format. As far as I can tell the date() function formats the time(). Any ideas on how I can get the mysql datatype to work date()?

[thetestingsite]

use a variation of split or explode and mktime to convert the datetime field into a timestamp, then you can just pass that variable (the timestamp) to the date function.

Hope this helps.

[james438]

currently the datetime is in the format of 2008-02-26 00:00:00 and the timestamp is just a huge number of seconds as counted from 1970. How would you convert from one format to the other?

(gonna get a haircut. I'll be back in a bit)

I could use some substr_replace(), explode(), and substr(), math, and ceil() and/or floor() to change it to the right format, but I am trying to be conservative if I can If I can't that's fine too.

[james438]

there is the MySQL date_format() function. How can I retrieve the date using date_format() from row 3 of a table?

[james438]

$date=str_replace(":"," ",$date);
$date=str_replace("-"," ",$date);
$date=explode(" ",$date);
$date=mktime($date[3],$date[4],$date[5],$date[1],$date[2],$date[0]);
$date=date("n/j/Y",$date);

I was over thinking it. The above code is really easy and is much simpler than what I was trying to do earlier.

[Medyman]

there is the MySQL date_format() function. How can I retrieve the date using date_format() from row 3 of a table?

Since you asked...

$sql = mysql_query("SELECT *, DATE_FORMAT(date, '%M %D, %Y') as date from table WHERE id='3'");