[thelist] MySQL date format

[Max Schwanekamp]

Symeon Charalabides wrote:
> Thanks, I know how to use DATE_FORMAT to return "Thursday, 1 December 2005". It's 
> the " - 2 " in my original request I'm not sure it's possible. I tried several 
> variations of
> DATE_FORMAT(day, "%W, %e - %e+1 %M %Y")

Not sure I understand what you're after either.  But I think you want to 
use DATE_ADD or similar[0].  DATE_FORMAT is really just a string 
formatting function for datetime data, and the second argument is a 
string of format specifiers that tells MySQL how to format the date -- 
incrementing operations are not part of the function.  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

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.

[0] http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

-- 
Max Schwanekamp http://www.neptunewebworks.com/