[thelist] mySQL DATE_FORMAT question

Simon Willison cs1spw at bath.ac.uk
Tue Jul 29 12:07:01 CDT 2003


Hi Tom,

Tuesday, July 29, 2003, 5:42:25 PM, you wrote:
> hello,

> I'm trying to format my date using the DATE_FORMAT function of mySQL.
> Here is my query:

> SELECT  
>   articleTitle, 
>   articleID, 
>   articleBlurb, 
>     DATE_FORMAT('dateCreated', '%b %D %Y')
> FROM 
>   article 
> LIMIT 3

> 'dateCreated' in my date field in the article table. I tried with and
> without quotes, both times getting the "Undefined index: dateCreated"
> error.

You definitely need to lose the quotes. What type is the dateCreated
column? It needs to be one of the mySQL bulit in date types for the
DATE_FORMAT function to work. Personally I always use MySQL with PHP
and use PHP's excellent date() function for date formatting; I've
written up some thoughts on date handling in MySQL here:

http://simon.incutio.com/archive/2003/07/11/storingDatesInMySQL

> Also, related question - instead of LIMIT 3, I'd like to get the 3
> LATEST articles. I know that the first 3 won't necessarily be that.
> How can I select the latest 3 by date?

SELECT
 ...
FROM
 article
ORDER BY
 dateCreated DESC
LIMIT 0, 3

The DESC is to place them in descending order, then the LIMIT 0, 3
grabs three starting from the top of the newly ordered result set.

Hope that helps,

Simon Willison
-- 
http://simon.incutio.com/



More information about the thelist mailing list