[SQL Basic] How to work with Date Functions in SQL — SQL DATE_FORMAT, DATEDIFF

SQLGate

10 Oct 2018 • 7 min read

#16. Marketing/Data Analysis for Beginners

Our SQL tutorials so far have covered how to look up data, navigate through it, and perform simple calculations. But in practice we want to to extract specific data from specific tables, not all the data in all the tables. The queries we have learned so far can filter most of that information, but we still need to cover a few more. Here are a few more queries and functions that are essential to your business.

There are certain standards that cannot be omitted from marketing and data analysis. The most important criteria when looking at existing customers, new customers, sales and other data is the date. In this tutorial, we will learn how to handle dates with SQL queries.

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN
📌Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
📌Lesson 15: SUBQUERIES

#Table of Contents

Query 1. Find the number of rentals that occur on a daily basis
SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

Query 2. Find which day of the week has the most rentals
SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

Query 3. Find the rental duration by calculating the difference between the rental date and return date
SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;

Before we dive into the information of the rental table, we need to become familiar with the date function and format of the SQL query. Let’s first run a query that prints the current date using DATE_FORMAT.

#DATE_FORMAT

Think of DATE_FORMAT as a column name and plug it in after your SELECT query, then specify a factor that has to do with the date. In our case, we want today’s date (now()). Next, we’ll specify the format. Using commas, spaces, and dashes, we can rearrange the date in any format we want! For our example today, we will demonstrate two: First, year-month-day followed by the name of the day(‘%Y-%m-%d, %a’). Second, day-month-year preceded by the name of the day(‘%W, %d-%m-%y’).

Try these:

SELECT DATE_FORMAT(now(), ‘%Y-%m-%d, %a’);
SELECT DATE_FORMAT(now(), ‘%W, %d-%m-%y’);

⭐️SQLGate provides a multi-query function that enables you to run two or more queries simultaneously and view query results at the same time!

Here is a table of helpful shortcuts you can use:

Q1. Find the number of rentals that occur on a daily basis and organize by date (using COUNT and DATE_FORMAT)

First, let’s look at the rental history data in the rental table.

SELECT * FROM rental;

The table contains the following information:

- Rental ID: (rental_id)
- Date of the rental: (rental_date)
- Inventory ID: (inventory_id)
- Date of return: (return_date)
- Customer ID:(customer_id)
- Staff ID: (staff_id)

Let’s say you want to look at the number of movie rentals that occur at the rental store on a daily basis. It’s simple. We can easily do this using the DATE_FORMAT query along with previously learned COUNT (tutorial 9) and GROUP BY (tutorial 11).

This time, instead of using now() we will designate rental_date as the column we want date information to be pulled from, and specify the format as year-month-day. Let’s also rename this new column ‘date’.

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’ ) AS date,

🔑 Remember to capitalize the Y in %Y so the full 4 digit year shows!

To find the number of all rentals, use COUNT and (rental_id), then rename that new column as count_rental. We’re using ID here because every movie has a unique ID assigned to them. Specify the table we want all this information from (rental):

COUNT(rental_id) AS count_rental FROM rental

And finally organize the information date with GROUP BY. Your final query should look like this:

SELECT DATE_FORMAT(rental_date, ‘%Y-%m-%d’) AS date, count(rental_id) AS count_rental FROM rental GROUP BY date;

🔑Tip: You can use F9 in SQLGate to instantly clean up your query!

This query returns the number of rentals on every day available on the record. Although it doesn’t offer exact time (00:00:00), it does give the year-day-month of the rental starting from 8 rentals on May 24, 2005.

Q2. Find which day of the week has the most rentals (using GROUP BY and ORDER BY)

Now that we’ve checked the number of movie rentals on a daily basis, let’s check the number of movie rentals on a week day basis. We will use the same data in the rental table and the query statements are very similar. The only thing we’ll change is the date output format.

Before we used ‘%Y-%m-%d’ to find the full date of the rental day. But in this next query, we only want the name of the week-day. If you check the handy table we provided in this tutorial, you will see to get this information we need to use a capital W (%W).

Just like Query 1, use the rental_date column as a basis for the date information(DATE_FORMAT(rental_date,). For the format, clarify that you only want the name of the day(%W). And rename the new column with AS.

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week

Next use COUNT to find the sum of the rentals and rename that column as well:

count(rental_id) AS count_rental

Clarify the name of the table:

FROM rental

Organize the information with GROUP BY and ORDER BY using the new column names we just made:

GROUP BY day_of_the_week ORDER BY count_rental DESC

Put it all together!!

SELECT DATE_FORMAT(rental_date, ‘%W’) AS day_of_the_week, count(rental_id) AS count_rental FROM rental GROUP BY day_of_the_week ORDER BY count_rental DESC;

🔑 Once again, don’t forget you can use F9 in SQLGate to tidy the query.

As you can see in the results, the largest number of loans occurred on Tuesday.

Q3. Find the rental duration by calculating the difference between the rental date and return date

Still using the information in the rental table, let’s calculate the rental period, rental date and the return date for each movie. We can do this using the query DATEDIFF.

#DATEDIFF

DATEDIFF calculates the number of days between two date values, date1 and date2. In our example, we want to know how long the movie was rented. So we will calculate the difference between the day it was rented out (rental_date) and the day it was returned (return_date). Go ahead and rename the new column to Rental Duration:

DATEDIFF(return_date, rental_date) AS rental_duration FROM rental

Earlier we mentioned we also want to see the rental ID, customer ID, rental date and return date, so specify those in the SELECT line.

SELECT rental_id, customer_id, rental_date, return_date

Of course, don’t forget to clarify which table you want all this info from:

FROM rental;

Put it all together and it should look like this:

SELECT rental_id, customer_id, rental_date, return_date, DATEDIFF(return_date, rental_date) AS rental_duration FROM rental;