Welcome to WebmasterWorld Guest from 184.108.40.206
Problem is, when I pull this information out from the db for screen display, my current client freaks.
Instead of the current display of 2006-03-02 19:13:37, he wants it to show March 2, 2006, 7:28pm.
Easy, of course, when I was just using string data... but now, oh, no.
How does one go about converting an accurate datetime type to something like the above? Can it be done?
list($yr,$mon,$day) = split('-',$row->fixturedate);
$display_date = date('D j M Y', mktime(0,0,0,$mon,$day,$yr));
This looks like my DB field is just a date rather than datetime but this can be tweaked for that no problem.
Hope that's helpful
SELECT DATE_FORMAT(thedate, '%M %e, %Y, %l:%i%p') as newdate FROM your_table;
SELECT *, DATE_FORMAT(thedate, '%M %e, %Y, %l:%i%p') as newdate FROM your_table;
It will return a field called 'newdate' with the formatting you want.
See this page [dev.mysql.com] at mysql.com/doc for all the formatting options.
Nevertheless, we've always tried to do the date formatting in SQL rather than in PHP/Perl just because it seems to be a bit faster and more elegant, but I think it's a matter of personal preference.
If you think you might have to switch to MSSQL, Oracle, etc at a later time, I would suggest maybe sticking all your date formatting selects into a separate function, and use *that* to call the "select DATE_FORMAT(blah, 'blah')". That way, if you do switch, there's only one place to change it.
We switched from Oracle to MySQL at one point, and OY! The amount of code that had to be fixed due to the differences between Oracle and Mysql date stuff was painful. If we'd been smart enough to put a "get_date" function or something, then we would have just had to change that.
UNIX_TIMESTAMP(time) AS timeand then formatting the date in php with
I've also found
strtotime()very useful on some occasions--it can figure out a lot of different formats.