SQL is a programming language we use to interact with relational databases. SQL databases contain tables, which contain rows of data. These tables can contain a wide range of data types.
In this article, you'll learn how MySQL functions help make date management very easy.
These functions help perform various tasks. Some perform simple tasks like adding days to dates, finding how many days are between two dates, or even more complicated tasks like how to tell how far into a year a date is by number of days.
Before proceeding, keep in mind that this article was written on
2023-01-24. So your results on running the queries here might be slightly different based on when you read it.
How to Use the
CURRENT_DATE Function in SQL
This function returns today's date in the format 'YYYY-MM-DD'. It is one of the simplest MySQL functions to use. It takes no arguments at all.
This function has synonymous functions that work just the way it does:
CURRENT_DATE() will return the exact same result as
How to Use the
ADDDATE Function in SQL
This functions performs additions, or subtractions, on date values. It takes an interval that can be in days, or months, or even years. This interval can be positive or negative. The function takes this format:
ADDDATE(date/expr, INTERVAL expr unit);
date/expr refers to the base date value to be added to or subtracted from. And the
INTERVAL is a constant keyword that has to come before the
expr that is used to set the value of the increment in numbers. Finally, you have the unit, which can be
quarter or even
unit can also be a smaller value like
second or even
microsecond. Check the MySQL docs for more possible values.
This functions works exactly the same as the
DATE_ADD and you can use them interchangeably.
Using ADDDATE, you can find the date of 45 days from today like this:
SELECT ADDDATE(CURRENT_DATE, INTERVAL 45 DAY); -- Returns 2023-03-10
To get the date of the day 7 months and 3 weeks ago, use the ADDDATE like this:
SELECT ADDDATE( ADDDATE(CURRENT_DATE, INTERVAL -7 MONTH), INTERVAL -3 WEEK ); -- Returns 2022-06-03
Here, we called the ADDDATE function twice. First, to get the date of 7 months ago. Then, we called it again to get the date of 3 weeks before that time.
A common use case of ADDDATE in real life applications is to get data values to be used in a WHERE clause as a range.
For example, if you had an
employees table with a
hiredate field that stores their resumption date. To see all employees that resumed in the past year (where
hiredate > the date of a year ago), use ADDDATE like this:
SELECT * FROM employees WHERE hiredate > ADDDATE(CURRENT_DATE, INTERVAL -1 YEAR);
Another common case would be when you have to filter by a time range. In a
songs table with a
released field, to fetch all songs released in the last three weeks except for the ones released this week, use ADDDATE like this:
SELECT * FROM songs WHERE released BETWEEN ADDDATE(CURRENT_DATE, INTERVAL -3 WEEK) AND ADDDATE(CURRENT_DATE, INTERVAL -1 WEEK);
How to Use the
DATEDIFF Function in SQL
This function returns the number of days between two dates. It takes in the two dates to be subtracted. Let's use
DATEDIFF to find the number of days between today and
SELECT DATEDIFF('2023-03-10', CURRENT_DATE); -- Returns 45
Rearranging the dates and calling the function again results in a difference in the response:
SELECT DATEDIFF(CURRENT_DATE, '2023-03-10'); -- Returns -45
You can use this function with the
ABS function to get the absolute value and not have issues with the negative sign or value.
SELECT ABS(DATEDIFF(CURRENT_DATE, '2023-03-10')); -- Returns 45
This is very useful when you have to return data with respect to time. `For example, in many blogs, you see a part that says something like 'Posted 7 days ago'. You can use the
DATEDIFF to get this value easily.
How to Use the
DATE_FORMAT Function in SQL
This function lets you present your data anyhow you want it. This is a very useful function. It takes in the date to be formatted, and also a string representing the desired format. The function takes this format:
The format string can be of any length and each character in it defines a specific format and must be prefixed by the percentage symbol,
%. For example, given the date
2023-03-10, you can present this as
Fri 10th March, 2023 like so:
SELECT DATE_FORMAT('2023-03-10', '%a %D %M, %Y');
Here, we passed in the format string
'%a %D %M, %Y'. But, what does this truly mean? Here's a few things to note:
- The provided format string,
'%a %D %M, %Y', is exactly the same shape as the result,
Fri 10th March, 2023. This means you can shape the result anyhow you like – even the space characters matter. Every character in the format string is returned as part of the result, except it is prefixed using the percentage sign, then it is read as a format character. For example, rewriting the format string to
'45 days from today is %a, %D day of %M, %Y'will result in
45 days from today is Fri, 10th day of March, 2023.
aused results in the abbreviated weekday name, Fri.
Dreturned the day of the month with English suffix, 10th.
Mreturned the name of the month, March.
Yreturned the year, 2023.
There are many more characters that you can use in the format string, and you can find them here.
How to Use the
MIN Functions in SQL
While these functions aren't limited or specific to date data type, they are very useful when working with dates. You can use the MAX to find the latest record in a table. You can use the MIN to find the oldest record in a table.
In a table of
employees, with a
birthday field storing their date of birth, you can find the oldest employee using the MAX function like this:
SELECT * FROM employees WHERE birthday = (SELECT MAX(birthday) from employees);
Or alternatively, like this:
SELECT * FROM employees ORDER BY birthday DESC LIMIT 1;
You could get the youngest employee using the MIN function:
SELECT * FROM employees WHERE birthday = (SELECT MIN(birthday) from employees);
SELECT * FROM employees ORDER BY birthday LIMIT 1;
I hope you now understand the MySQL date functions we discussed here, their variations and arguments, and when to use them so you can write better queries. You can find more of these functions here.
If you have any questions or relevant advice, please get in touch with me to share them.