MySQL Between Dates Query Search: A Guide With Code Examples

What is MySQL?

New to MySQL? Don’t be deterred by this technical sounding name, or the idea of “queries” or “commands” it’s pretty easy to learn and it’s going to make your data collection and organization so much easier. MySQL is a relational database management system (RDBMS). A RDBMS is just a fancy way of saying a database system that stores data in tables, fields and records. SQL stands for structured query language, which essentially is a computer language that lets users add, delete, edit, organize, and store data in these tables, fields, and records. “My” of MySQL is actually the name of the developer’s daughter. How cute. MySQL is an open source RDBMS, which means it’s available to everyone, everywhere, for free. MySQL is extremely popular for its ease of use, flexibility and quick processing. It’s essential for almost every open source PHP application. Learn about MySQL and PHP fundamentals.

How does MySQL work?

Okay, so let’s say that you have an online shop that sells hand knit socks. Your store is going to have a user login that allows your customers to login to make a purchase. This way their billing and shipping information is stored, their user name, their activity, etc. It’s pretty standard to have users login to access information or a service on a website. You know, like Amazon.com, Etsy.com, Netflix.com, etc. So. You’re customer creates a login username and enters all of the billing information to order 5 pairs of socks on January 1st, 2013. You know the order was placed because you probably receive an email regarding it, and the customer knows because they get a confirmation email. But where does this information actually go? How can that same customer login to your site again tomorrow and access the information they entered yesterday? Hello, MySQL. Behind the scenes the user data is being entered into a database table that you (or your web developer) created upon registering your site. In this table, you have indicated important fields (like First Name, Last Name, Address, Email Address, etc.) that you would like your customer’s records to be put into. The records would be something like: Tracy, Sloan, 123 Potters St., [email protected], as per the above example.

Why do you need to search between dates?

If you have a hand knit sock store and you have hundreds of customers a month, you may want to look at people who registered for your site in, say, April. You may want to see how many orders were placed between July and October. You may want to see how many people ordered from your site for the entire year of 2013. All of these things are possible by using what we call a BETWEEN query. Basically, in SQL, you write a query, or command, to tell the database do what you want it to do. There are tons of queries to learn, but here, we’ll cover the BETWEEN query in regards to dates. If you’re ready to dive in and enhance your web developer skills, take this MySQL Database training course.

How to Search Between Dates

Alright, so you want to find data between two different dates. We’re going to go over how to find data between two years, between two months (of all years), between two days and between two days or months of a specified year. That way, no matter what you’re looking for you’ll be able to gather it no problem.

When we search MySQL for records between two given values we use what is called a BETWEEN Query. This says that between a lower value and an upper value the records will be called so you can view them. The database name is where you will put your database table name that you wish to sort records in.

1. Find records between two years. Say, for example, you want to locate orders placed between 2009 – 2012. The BETWEEN query for that is:

  SELECT * FROM table name WHERE year( dt2 ) between 2009 and 2012

2. If you are looking for a little bit more specific information, you can always query between certain months. This command will return the records for every year in your table between the months specified. So, if you’re looking for orders placed between March and April, this query will return all data between March and April for 2009, 2010, 2011 and 2012 (or whatever years you have included in your table).

   SELECT * FROM table name WHERE month(dt) between '03' and '04'

You’ll notice that we call the months by using their numeric value, March (03), April (04).

3. Chances are you probably want to call your data between certain months of a certain year. We can do that pretty easily. The query you will use to find all records between March and April of 2012 will look like:

SELECT * FROM table name WHERE date between '2012-03-01' and '2012-04-30'

4. Alright, and lastly, if you are trying to locate records between two specific dates, say, March 15th, 2012 and April 15th, 2012, the query would be:

SELECT * FROM table name WHERE date BETWEEN '2012-03-15' AND '2012-04-15'

The format of the date is always year-month-day.

If you are new to MySQL and want to learn more about queries, commands, and functions, try starting off with MySQL training for beginners.