MySQL DATE_FORMAT()

When getting dates out of a database why not have mysql convert them to the format you are going to use as it gets them? The only reasonable answer is “because I didn’t realise you could”.

SELECT DATE_FORMAT(date_column, '%D, %d %M %Y') AS my_nice_date FROM somewhere

Now you can do this (in PHP): $row[‘my_nice_date’] which will display something like this:


Fri, 04 Dec 2009

…which is nice but what if you need the same date in a different format on the same page? Easy.

SELECT DATE_FORMAT(date_column, '%D, %d %M %Y') AS my_nice_date, DATE_FORMAT(date_column, '%e/%c/%Y') AS my_nice_date_two FROM somewhere

Now you will have $row[‘my_nice_date’] and $row[‘my_nice_date_two’]. Which will look like these, respectively:


Fri, 04 Dec 2009
4/12/2009

Boomting.

Advertisements

About Mr Chimp

I make music, draw pictures, browse the internet, programme, and make sweet, sweet cups of tea until the early hours.
This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

3 Responses to MySQL DATE_FORMAT()

  1. Here is a good website for helping you format your dates using the date_format mysql function.

    • Skilldrick says:

      Thank you! I just needed to do this, and I thought, oh yeah, Jake wrote something about that!

      One thing I don’t like is that the format string is very similar to http://php.net/manual/en/function.date.php but with some subtle differences. E.g., for what I’m using it for I’d prefer not to have the leading zero on the date, which is %j in PHP and %e in MySQL. Grrrr.

      • Mr Chimp says:

        Yeah, it does get a bit confusing. Also if you want to any amount of plain text between the codes the escaping can get a bit awkward. Ah well, no-one said it would be easy!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s