[thelist] MySQL date format
Max Schwanekamp
lists at neptunewebworks.com
Mon Nov 28 11:36:40 CST 2005
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/
More information about the thelist
mailing list