homepage Welcome to WebmasterWorld Guest from 50.19.169.37
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Display SQL Date as mm/dd/yy
Phillips126




msg:3964246
 5:33 pm on Aug 2, 2009 (gmt 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!

 

rocknbil




msg:3964668
 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved