Introduction

In this article, we discuss some simple date functions that MySQL offers. In Part 6 of this series, we covered the essential aggregation functions widely used in SQL statements. Check out the link below to previous articles.

MySQL Part 6: Aggregations

To begin your MySQL session in the goormIDE, please be sure to type this code into the terminal.

mysql-ctl cli

Please ensure that the bike_shop.sql file is selected and available. If not, double-click it on the left-hand side. This is where we will type most of our code. If there is information saved in there, go ahead and clear out that information.

Dates in MySQL

Dates in MySQL are some of the more challenging problems you will face. In this article, we will present some of the more common date functions used. In the query below, all columns and rows are from the orderlines table. We will be using the order_date column many times in the examples that follow.

USE bike_shop_app;

SELECT * FROM orderlines;

DAY Function

The DAY function allows you to pull the day of the month out of the order_date column. For example, on January 10, 2011, the DAY function extracted the 10th from the order_date field.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    DAY(order_date) 
    FROM orderlines;

DAYNAME Function

The DAYNAME function defines the day of the week (e.g., Monday, Tuesday, etc.).

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    DAYNAME(order_date) 
    FROM orderlines;

DAYOFWEEK & DAYOFMONTH Function

The DAYOFWEEK function converts the day of the week into a numerical value for Monday, Tuesday, etc. The DAYOFMONTH function defines the day of the month, similar to the DAY function described above. See some of the examples below.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    DAYOFWEEK(order_date),
    DAYOFYEAR(order_date)
    FROM orderlines;

MONTH & MONTHNAME Function

The MONTH function converts the month of the date into a numerical value for January, February, etc. The MONTHNAME function lists the full name of the month. See some of the examples below.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    MONTH(order_date),
    MONTHNAME(order_date)
    FROM orderlines;

DATE_FORMAT Function

The DATE_FORMAT function gives you the ability to format dates into a more standard format. There are dozens of date formats. For a complete list of the formatting options, check out the link below.

https://www.w3schools.com/mysql/func_mysql_date_format.asp

The example below uses one of the more popular date formats. It transitions '2011-01-07' into a more traditional '01/07/2011'.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    DATE_FORMAT(order_date, '%m/%d/%Y')
    FROM orderlines;

DATE_DIFF Function

The DATE_DIFF function is convenient when calculating the variance between two dates. The query below calculates the number of days between the current date (CUR_DATE()) and the order_date. See the example below.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    CURDATE() AS today_date,
    order_date,
    DATEDIFF(CURDATE(), order_date) AS date_diff
    FROM orderlines;

DATE_ADD Function

Similar to the DATE_DIFF function, we can also add intervals to dates. For example, to add a month to the order_date, we can use the DATE_ADD function as shown below.

USE bike_shop_app;
 
SELECT 
    order_id,
    order_line,
    order_date,
    DATE_ADD(order_date, INTERVAL 1 MONTH) AS date_diff
    FROM orderlines;

Conclusion

As mentioned earlier, date functions can get tricky. We only scratched the surface with some of the functions available in MySQL. There are plenty of resources to help you with your problem set. In the next article, we will discuss logical operators.

MySQL Part 8: Logical Operations

For a complete list of the entire set of MySQL-related articles, please see the links below.

MySQL Part 1: goormIDE
MySQL Part 2: Create Database
MySQL Part 3: Create Tables
MySQL Part 4: String Functions
MySQL Part 5: Refining Select Statements
MySQL Part 6: Aggregations
MySQL Part 7: Dates
MySQL Part 8: Logical Operations
MySQL Part 9: Joins

Search This Blog

Data Exploration

MySQL Part 7: Dates