[thelist] MySQL date format

[Symeon Charalabides]

> Anyway, AFAIK 
> you'll need to select three dates and CONCAT them for your desired 
> format. Assuming thedate is a datetime column type, it might be 
> something like this:
> 
> SELECT CONCAT(
>    DATE_FORMAT(mydate, "%W, %e - "),
>    DATE_FORMAT(DATE_ADD(mydate, INTERVAL 1 DAY), "%e"),
>    DATE_FORMAT(mydate, " %M %Y")
> ) as thedate
> Example output:
> Saturday, 26 - 27 November 2005
> Tuesday, 26 - 27 July 2005
> Friday, 3 - 4 June 2005

Thanks, this is an excellent example.
> 
> Of course this format fails to take into account the last day of the 
> month or year (giving you e.g. Saturday, 31 - 1 December 2005), so you 
> would probably need to improve this.  All in all, it's usually better to 
> use PHP (or whatever) to format your dates for ouput.

True, this is what I ended up doing. I was wondering if it was possible to bring 
up such a string (or some variation thereof) with a simple SELECT, as it's faster 
than digging up the conventional date, then formatting it with PHP. As it turns 
out this isn't possible as the SELECT needed is so complex that PHP probably does 
it faster. So I may as well just write

date('l j - ', strtotime($row['day'])).
date('l j ', strtotime($row['day'])+60*60*24).
date('F Y', strtotime($row['day']))

like I used to. So much for learning new tricks...

Symeon Charalabides (cosmopolite trainee)
-------------------------------------------------
http://www.systasis.com