Search functions against DATETIME in MySQL [using PHP]

When you are using a database for any large scale application, it is always a good idea to make a note of when things happen, i.e. when a page was created or when a page was updated. The easiest way of doing this is to pass NOW() through your SQL to your database to add the date and time to a DATETIME field, whereby you end up with data looking like this in your table:

2010-10-13 18:32:45

2010-10-13 18:28:01

2010-10-13 18:29:17

In its raw form, the SQL for performing this function will look something like this:

SELECT * FROM your_table WHERE DATE_FORMAT(column_name, '%Y-%m-%d')= 'your_date'

The SQL statement works in two stages:

1. MySQL first formats the data into your prescribed format, in my case, this was YEAR (%y), MONTH (%m), DAY (%d).
2. Now that MySQL has sorted the data internally, it performs the comparison part of the statement against your formatted data using the WHERE clause.

Of course, the internal functions that MySQL provides performs the vast majority of the hard work for you. The key to using this solution is to provide your string in the same format as the data is stored. In my case, the string I needed to run a comparison on was a date, the time wasn't a necessary part of the clause, so I provided:

'2010-10-13'

So my SQL looked like a little bit like this:

SELECT * FROM my_dates WHERE DATE_FORMAT(my_date_time, '%Y-%m-%d')= '2010-10-13'
(Notice the hyphen as the separator, this may differ in your case)

%Y = 4 digit numeric YEAR,
%m = 2 digit numeric MONTH,
%d = 2 digit numeric DAY

There are many other options for date formatting, all of which can be found here on the MySQL website:
http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-format

I have also written a different method of extracting data using a date, you can find that here.

I hope this helps...