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



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 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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s