[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