Dates are an integral part of any programming language, and SQL is no exception. When you insert data into your SQL database, you can add a date and query the database based on that date.
In this article, you’ll learn about DATE functions in SQL and how to query a database with dates. We'll also take a look at some time functions.
What We'll Cover
- Date Functions in SQL
- How to Query a Database Based on Dates
Date Functions in SQL
The ADDDATE() function does what the name implies – it adds an interval to a
You can use the
ADDDATE() function in this format:
ADDDATE(date, INTERVAL value addunit).
dateis the date you’re working with. For MySQL, the date format is YYY-MM-DD and is required.
INTERVALis a required keyword
valueis an integer representing the interval you want to add
addunitis what the interval should represent. That is year, month, day, hours, minutes, seconds, and other relevant units.
For example, running the query below returns '2022-10-22'. This means that 10 days got added to '2022-10-12'.
SELECT ADDDATE("2022-10-12", INTERVAL 10 DAY);
If you want, you can use it with month or year:
The CURRENT_DATE() function shows exactly what it says – the current date. It returns the date in the YYYY-MM-DD format.
SELECT CURRENT_DATE() returns the date I started writing this article:
The CURRENT_TIME function shows the current time.
The current timestamp function returns the current date and time. It’s the combination of CURRENT_DATE() and CURRENT_TIME().
The NOW() function returns the current date and time.
You can use the DATE function to extract the date part of a timestamp.
SELECT DATE("2022-11-14 12:00:00");
The DATE_SUB() function subtracts a day, month, or year from a date.
In the query below, I subtracted 10 days from the date I started writing this article:
SELECT DATE_SUB("2022-11-14", INTERVAL 10 DAY);
The DATEDIFF() function returns the number of days between two dates.
SELECT DATEDIFF("2023-11-14", "2022-11-14");
This function returns the day within a specified date.
The MONTH function returns the month in a specified date.
The YEAR function returns the year in a specified date.
How to Query a Database Based on Dates
To show you how to query a database using dates, I’ll be using the table below:
To select some particular date between one date and another, you can use the
AND keywords while specifying the dates.
In the query below, I select all the items added to the database in 2021:
SELECT * FROM brands WHERE date_added BETWEEN "2021-01-01" AND "2021-12-31";
NOW() functions, I was able to get the items added to the database in the last 3 months:
SELECT * FROM brands WHERE date_added > DATE_SUB(NOW(), INTERVAL 3 MONTH);
This article showed you some important functions you can use to work with dates and query your database within SQL.
If you find the article useful, don’t hesitate to share it with friends and family.
Thanks for reading.