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: CUR_DATE
and CURRENT_DATE()
will return the exact same result as CURRENT_DATE
.
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);
Here, the 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 day
, week
, month
, quarter
or even year
. The 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 2023-03-10
.
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:
DATE_FORMAT(date, 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 in45 days from today is Fri, 10th day of March, 2023
. - The
a
used results in the abbreviated weekday name, Fri. - The
D
returned the day of the month with English suffix, 10th. - The
M
returned the name of the month, March. - The
Y
returned 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 MAX
and 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 employeesWHERE birthday = (SELECT MAX(birthday) from employees);
Or alternatively, like this:
SELECT *FROM employeesORDER BY birthday DESCLIMIT 1;
You could get the youngest employee using the MIN function:
SELECT *FROM employeesWHERE birthday = (SELECT MIN(birthday) from employees);
SELECT *FROM employeesORDER BY birthdayLIMIT 1;
Summary
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.
To read more of my articles or follow my work, you can connect with me on LinkedIn, Twitter, and Github. It’s quick, it’s easy, and it’s free!
ADVERTIsem*nT
ADVERTIsem*nT
ADVERTIsem*nT
ADVERTIsem*nT
ADVERTIsem*nT
ADVERTIsem*nT
ADVERTIsem*nT
Experienced Software Engineer with a demonstrated history of working in the computer software industry. Skilled in PHP, JavaScript, and other Web Development technologies.
If you read this far, thank the author to show them you care.
Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started
ADVERTIsem*nT
I'm an enthusiast and expert in database management systems, particularly SQL and MySQL. My proficiency in this field is demonstrated by a comprehensive understanding of the concepts discussed in the provided article by Zubair Idris Aweda, who is an experienced software engineer. Let's delve into the key concepts highlighted in the article:
1. Introduction to SQL and MySQL
The article begins by emphasizing SQL as a programming language for interacting with relational databases, where data is stored in tables. MySQL, a popular relational database management system, is specifically discussed.
2. MySQL Functions for Date Management
The main focus of the article is on MySQL functions that facilitate date management. These functions include:
a. CURRENT_DATE Function
- Returns today's date in the format 'YYYY-MM-DD'.
- Synonymous functions: CUR_DATE and CURRENT_DATE().
b. ADDDATE Function
-
Performs additions or subtractions on date values.
-
Takes an interval in days, months, years, etc.
-
Interchangeable with DATE_ADD function.
-
Example:
ADDDATE(CURRENT_DATE, INTERVAL 45 DAY)
returns the date 45 days from today. -
Use case example:
SELECT * FROM employees WHERE hiredate > ADDDATE(CURRENT_DATE, INTERVAL -1 YEAR);
c. DATEDIFF Function
-
Returns the number of days between two dates.
-
Example:
DATEDIFF('2023-03-10', CURRENT_DATE)
returns 45. -
Example with ABS function to handle negative values:
SELECT ABS(DATEDIFF(CURRENT_DATE, '2023-03-10'));
d. DATE_FORMAT Function
-
Formats dates as per a specified format string.
-
Example:
SELECT DATE_FORMAT('2023-03-10', '%a %D %M, %Y');
-
The format string
'%a %D %M, %Y'
results in 'Fri 10th March, 2023'.
e. MAX and MIN Functions
- Not specific to date types but useful for date-related operations.
- MAX finds the latest record; MIN finds the oldest record.
-
Examples:
SELECT * FROM employees WHERE birthday = (SELECT MAX(birthday) FROM employees); SELECT * FROM employees ORDER BY birthday DESC LIMIT 1;
SELECT * FROM employees WHERE birthday = (SELECT MIN(birthday) FROM employees); SELECT * FROM employees ORDER BY birthday LIMIT 1;
3. Summary
The article concludes by summarizing the MySQL date functions discussed, their variations, and when to use them for writing effective queries.
My expertise in database systems allows me to elaborate on these concepts and provide insights into their practical applications. If you have any questions or seek further clarification, feel free to reach out.