MySQL Date Functions

I found a useful MySQL trick which would have saved me some time a when making a site last year. It’s actually a fairly standard procedure but if you’re learning as you go it’s not exactly obvious. It goes something like this (date_col is a sql date column):

SELECT * FROM table WHERE YEAR(date_col) = 2009

You can probably guess what it does – it gets all records which are dated 2009. You can do MONTH() and DAY() as well.

You can use it in different ways, too. How about if you’ve got a load of news items and you want to know which year the first one was posted in? You do this:

SELECT YEAR( news_date ) AS 'year' FROM `news` ORDER BY news_date LIMIT 1

Or if you want the year of the latest one, this:

SELECT YEAR( news_date ) AS 'year' FROM `news` ORDER BY news_date DESC LIMIT 1

Have fun!

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.

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