Jamie Balfour

Welcome to my personal website.

Find out more about me, my personal projects, reviews, courses and much more here.

Part 3.6SQL dates

Part 3.6SQL dates

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.

Feedback 👍
Comments are sent via email to me.