Welcome to WebmasterWorld Guest from 54.158.228.55

Forum Moderators: open

Message Too Old, No Replies

Display SQL Date as mm/dd/yy

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

5+ Year Member



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)

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



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.

 

Featured Threads

Hot Threads This Week

Hot Threads This Month