SQL DATE – Function, Query, Timestamp Example Format (2024)

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
  • ADDDATE()
  • CURRENT_DATE()
  • CURRENT_TIME();
  • CURRENT_TIMESTAMP();
  • NOW()
  • DATE
  • DATE_SUB
  • DATEDIFF
  • DAY
  • MONTH
  • YEAR
• How to Query a Database Based on Dates
• Conclusion

Date Functions in SQL

ADDDATE()

The ADDDATE() function does what the name implies – it adds an interval to a date or datetime.

You can use the ADDDATE() function in this format: ADDDATE(date, INTERVAL value addunit).

• date is the date you’re working with. For MySQL, the date format is YYY-MM-DD and is required.
• INTERVAL is a required keyword
• value is an integer representing the interval you want to add
• addunit is 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:

CURRENT_DATE()

The CURRENT_DATE() function shows exactly what it says – the current date. It returns the date in the YYYY-MM-DD format.

For instance, SELECT CURRENT_DATE() returns the date I started writing this article:

CURRENT_TIME();

The CURRENT_TIME function shows the current time.

SELECT CURRENT_TIME();

CURRENT_TIMESTAMP();

The current timestamp function returns the current date and time. It’s the combination of CURRENT_DATE() and CURRENT_TIME().

SELECT CURRENT_TIMESTAMP();

NOW()

The NOW() function returns the current date and time.

SELECT NOW();

DATE

You can use the DATE function to extract the date part of a timestamp.

SELECT DATE("2022-11-14 12:00:00");

DATE_SUB

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);

DATEDIFF

The DATEDIFF() function returns the number of days between two dates.

SELECT DATEDIFF("2023-11-14", "2022-11-14");

DAY

This function returns the day within a specified date.

SELECT DAY("2022-11-14");

MONTH

The MONTH function returns the month in a specified date.

SELECT MONTH("2022-11-14");

YEAR

The YEAR function returns the year in a specified date.

SELECT YEAR("2022-11-14");

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 BETWEEN and AND keywords while specifying the dates.

In the query below, I select all the items added to the database in 2021:

SELECT *FROM brandsWHERE date_added BETWEEN "2021-01-01" AND "2021-12-31";

Combining the DATE_SUB() and NOW() functions, I was able to get the items added to the database in the last 3 months:

SELECT *FROM brandsWHERE date_added > DATE_SUB(NOW(), INTERVAL 3 MONTH);

Conclusion

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.