Welcome to WebmasterWorld Guest from 54.82.29.141

Forum Moderators: open

Message Too Old, No Replies

Display SQL Date as mm/dd/yy

     
5:33 pm on Aug 2, 2009 (gmt 0)

New User

5+ Year Member

joined:July 31, 2009
posts:15
votes: 0


Hey Guys,

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)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 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.

 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members