| Welcome to WebmasterWorld Guest from 220.127.116.11 |
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
|Subscribe to WebmasterWorld|
|Display SQL Date as mm/dd/yy|
| 5:33 pm on Aug 2, 2009 (gmt 0)|
I am sorting a table on my website by the most recently added entry. I am using ORDER BY web_date DESC.
$result = mysql_query("SELECT * FROM websites ORDER BY web_date DESC LIMIT $offset, $records_per_page") or die(mysql_error());
The datatype was set to VARCHAR(10), however when looking at the two dates 8/10/09 and 8/2/09, it recognized the 10 as a 1, meaning 8/2/09 was "most recent."
To solve this, I switched the datatype to DATE (since I dont need the DATETIME time aspect). It is working great, however, it is displaying the date as YYYY/DD/MM.
Can anyone explain how to change the format to MM/DD/YY?
I know you do something like:
SELECT CONVERT(CHAR(8), GETDATE(), 112)
I am not sure how that is implemented in the actual code.
Could anyone explain how to change this?
Thanks in advance!
| 2:19 pm on Aug 3, 2009 (gmt 0)|
Use the date_format() function [dev.mysql.com].
Of course, you'll have to change your select to specific fields instead of *, or at least, add a new select field for it:
SELECT *,format(date_field,'%m/$d/%y') FROM websites ORDER BY web_date DESC LIMIT $offset, $records_per_page;
The formatted date will be the last value in the returned array.
All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
© Webmaster World 1996-2014 all rights reserved