Date functions allow you to manipulate date data stored in tables through SQL queries. Here will discuss only the MySQL Date Functions. The time functions will be followed after this tutorial. MySQL Date functions include functions to add or subtract two dates, find current date and system date and find intervals between two dates.
Note: In all the examples discussed the date values are used with SELECT statements. You can use these functions with table columns defined with date as datatype
Commonly used MySQL Date Functions
CURDATE(), CURRENT_DATE() or CURRENT_DATE
These three MySQL Date Functions allow you to get the current date. No arguments are needed to use these functions.
Example
SELECT CURDATE(); SELECT CURRENT_DATE(); SELECT CURRENT_DATE;
ADDDATE()
To get a date after or before a specific date you can use ADDDATE() functions. Its syntax is
ADDDATE(date_expr,no_of_days)
The first expression is the string representing date in ‘yyyy-mm-dd’ format. The second argument is the integer value to add the number of days to the date. The output is string representing date after adding the number of days. If the second argument is negative, you will get a prior date and if it is positive you get a future date from the date expression given in first argument
Example
SELECT ADDDATE('2022-01-30',31);
SELECT ADDDATE('2022-01-30',-31);
DATE()
There can be tasks where you will get date-time expression as input and you need only the date part of it. DATE function will be useful in such cases. Its syntax is
DATE(date_time_expression)
Example
SELECT DATE(‘2022-02-03 12:22:33’);
DATE_ADD()
The DATE_ADD function gives flexibility to specify interval to be added in a date in days, months or years. The syntax is
DATE_ADD(date_expr, INTERVAL interval_value INTERVAL_TYPE)
INTERVAL is the keyword. INTERVAL_TYPE can have values YEAR, MONTH or DAY to add interval in years, months or days.
Example
SELECT DATE_ADD('2022-02-03', INTERVAL 10 DAY);
SELECT DATE_ADD('2022-02-03', INTERVAL 2 MONTH);
SELECT DATE_ADD('2022-02-03', INTERVAL 2 YEAR);
DATE_SUB()
The DATE_SUB function gives flexibility to specify interval to be subtracted from a date in days, months or years. The syntax is
DATE_SUB(date_expr, INTERVAL interval_value INTERVAL_TYPE)
INTERVAL is the keyword. INTERVAL_TYPE can have value YEAR, MONTH or DAY to add interval in years, months or days.
Example
SELECT DATE_SUB('2022-02-03', INTERVAL 10 DAY);
SELECT DATE_SUB('2022-02-03', INTERVAL 3 MONTH);
SELECT DATE_SUB('2022-02-03', INTERVAL 3 YEAR);
DATEDIFF()
This function is used when you need to get time interval in days between two dates. If first date expression is earlier than the second, the days will be returned as negative integer else as positive number. The syntax is
DATEDIFF(date_expr1, date_expr2)
Example
SELECT DATEDIFF('2022-02-03', CURDATE());
SELECT DATEDIFF(CURDATE(),'2022-02-03');
MAKEDATE()
If you want to get the date of a specific day (1 to 365) of a specific year then this function will be handy. The syntax is
MAKEDATE(year_number, day_number between 1and 365)
Example
SELECT MAKEDATE(2022, 45);
SUBDATE()
This function is similar to ADDDATE(). You can get a date after or before a specific date. Its syntax is
SUBDATE (date_expr,no_of_days)
The first expression is the string representing date in ‘yyyy-mm-dd’ format. The second argument is the integer value to subtract the number of days from the date. The output is a string representing date after subtracting the number of days. If the second argument is negative, you will get a future date and if it is positive you get a prior date from the date expression given in first argument.
Example
SELECT SUBDATE(CURDATE(),20); SELECT SUBDATE(CURDATE(),-40);
SYSDATE()
This is a no argument function that returns date and time at the time query is executed. This is a very commonly used function to store date-time for maintaining log data like timestamp.
Example
SELECT SYSDATE();
These are the most versatile and helpful MySQL Date Functions that you can use to do date based calculations in your database tables.