MySQL provides several built-in functions for using dates. Whilst it is not the goal nor intention of this article in this tutorial to explain each of these, several of these functions have been explained on this page.
| Function | Description |
|---|---|
NOW |
Returns the current date and time |
CURDATE |
Returns the current date |
CURTIME |
Returns the current time |
DATE |
Extracts the date part of a date or date/time expression |
EXTRACT |
Returns a single part of a date/time |
DATE_ADD, DATE_SUB |
Adds or subtracts a time to/from a date |
DATEDIFF |
Returns the number of days between two dates |
DATE_FORMAT
|
Returns date/time data in different formats |
The information above was sourced from W3Schools.
Selecting dates
Dates work in the same manner as selecting other forms of data in MySQL provided there is no time added to the date. Assuming the following table called 'orders' is used:
| id | date_purchased |
|---|---|
| 1 | 2005-12-04 |
| 2 | 2005-12-04 |
| 43 | 2011-04-07 |
A simple SQL statement to find all purchases made on the 4th December 2005 would look like:
SELECT * FROM `orders` WHERE `date_purchased` = "2005-12-04"
The result would be:
| id | date_purchased |
|---|---|
| 1 | 2005-12-04 |
| 2 | 2005-12-04 |
However, when times are involved it becomes much harder. One such way of comparing whether something happens on a date when times are involved is to add times. For the following table:
| id | date_purchased |
|---|---|
| 1 | 2005-12-04 00:44:03 |
| 2 | 2005-12-04 13:06:52 |
| 43 | 2011-04-07 16:21:47 |
To obtain all purchases made on the 4th of December 2005 it's easy enough to append some time and compare using the BETWEEN
keyword:
SELECT * FROM `orders` WHERE `date_purchased` BETWEEN 2005-12-04 00:00:00 AND 2005-12-04 23:59:59
This way the results will be the same, since any date with a time between 00:00:00 and 23:59:59 is on the same day.
