[thelist] MySQL date format
Hassan Schroeder
hassan at webtuitive.com
Mon Nov 28 16:38:01 CST 2005
Symeon Charalabides wrote:
> Actually, I hadn't thought about it. The way I have it set
> up now, the output will be
>
> Wednesday 30 - Thursday 1 December 2005
>
> which is far from perfect. So, 2 more lines of code will be required to fix that.
> Trivial, of course, but thanks for the insight.
Just for fun; where `someday` is a DATE column in `sometable` --
+------------+
| someday |
+------------+
| 2005-07-13 |
| 2005-10-22 |
| 2005-11-30 |
| 2005-12-31 |
+------------+
SELECT
someday
, CONCAT(DATE_FORMAT(someday, "%W, %e")
, IF( (MONTH(someday) != MONTH(DATE_ADD(someday, INTERVAL 1 DAY)))
, DATE_FORMAT(someday, " %M")
,'' )
, DATE_FORMAT(DATE_ADD(someday, INTERVAL 1 DAY), " - %e")
, DATE_FORMAT(DATE_ADD(someday, INTERVAL 1 DAY), " %M %Y"))
FROM sometable;
+------------+------------------------------------------+
| someday | result |
+------------+------------------------------------------+
| 2005-07-13 | Wednesday, 13 - 14 July 2005 |
| 2005-10-22 | Saturday, 22 - 23 October 2005 |
| 2005-11-30 | Wednesday, 30 November - 1 December 2005 |
| 2005-12-31 | Saturday, 31 December - 1 January 2006 |
+------------+------------------------------------------+
Didn't account for the year in that last one, though, but I'd think
it unnecessary :-)
FWIW!
--
Hassan Schroeder ----------------------------- hassan at webtuitive.com
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
More information about the thelist
mailing list