MariaDB Date Add Days [With 21 Useful Examples]

In this MariaDB tutorial, we will discuss the implementation of MariaDB Date Add Days function with the help of a function and also look at several examples related to it. There are lists of the topic that comes under discussion:

• MariaDB Date Add Days
• MariaDB Date Add Days And Hours
• MariaDB Date Add Days And Minutes
• MariaDB Date Add Days Between Two Dates
• MariaDB Date Add Days Between
• MariaDB Date Add Days Count
• MariaDB Date Add Days Group By
• MariaDB Date Add Days Greater Than
• MariaDB Date Add Days Greater Than Today
• MariaDB Date Add Days Hours
• MariaDB Date Add Days In Month
• MariaDB Date Add Days Like
• MariaDB Date Add Days Not Working
• MariaDB Date Add Days Of Week
• MariaDB Date Add Days Per Day
• MariaDB Date Add Days Quarter
• MariaDB Date Add Days Regex
• MariaDB Date Add Days Seconds
• MariaDB Date Add Days Using Timestamp
• MariaDB Date Add Days Week
• MariaDB Date Add Days Year

MariaDB Date Add Days

Here we will understand and learn how to use the MariaDB DATE_ADD function to add days in date by query, which is explained with the help of an example.

The DATE_ADD method in MariaDB retrieves a date after a time/date period has been added. Let’s see the syntax of the MariaDB DATE_ADD function by the following query:

SYNTAX:

SELECT DATE_ADD( DATE, INTERVAL VALUE UNIT) FROM TABLE_NAME;

The syntax explanation:

• INTERVAL: INTERVAL is added on this date.
• VALUE: The length of the time/date interval to be added. This parameter accepts both positive and negative values.
• UNIT: The interval’s unit type, such as DAY, MONTH, MINUTE, HOUR, etc. One of the following possibilities exists:
  • MICROSECOND
  • SECOND_MICROSECOND
  • MINUTE_MICROSECOND
  • HOUR_MICROSECOND
  • DAY_MICROSECOND
  • SECOND
  • MINUTE_SECOND
  • HOUR_SECOND
  • DAY_SECOND
  • MINUTE
  • HOUR_MINUTE
  • DAY_MINUTE
  • HOUR
  • DAY_HOUR
  • DAY
  • DAY_MICROSECOND
  • DAY_SECOND
  • DAY_MINUTE
  • DAY_HOUR
  • WEEK
  • MONTH
  • QUARTER
  • YEAR
  • YEAR_MONTH

NOTE:

The DATE_ADD function will presume that the left-most component of the interval value was not submitted if we specify an intermediate value that is too brief for the unit you have specified.

Here is a sample example of the MariaDB DATE_ADD function by the following query:

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`,INTERVAL 25 MINUTE) FROM EMPLOYEE 
LIMIT 5;

In this preceding query, the MariaDB SELECT statement has been used the DATE_ADD function on the CURRENT_DATE column of the EMPLOYEE table. The main use is to add 25 minutes to all records of the CURRENT_DATE column by using the INTERVAL keyword inside parameters.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records of the EMPLOYEE table of USA by using the SELECT statement.

In this section, we have understood by the example how to use the MariaDB DATE_ADD function on the EMPLOYEE table in the query.

Read: MariaDB Check If Rows Exists

MariaDB Date Add Days And Hours

In this section, we will learn how to use the MariaDB DATE_FUNCTION with the DAYS_HOUR value in the parameter of the query. And it will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`,INTERVAL 5 DAY_HOUR) AS DAYS_HOUR FROM EMPLOYEE 
LIMIT 5;

As we see in the above query, we have used the DATE_ADD function on the CURRENT_DATE column from the EMPLOYEE table by using the SELECT statement. In the DATE_ADD function, we are adding 5 hours of a day to every record of the CURRENT_DATE column.

To shorter the function name, we have used the alias clause with AS keyword and given the name as DAYS_HOUR for the output column.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records of the EMPLOYEE table by using the SELECT statement.

We have used an example for making it understandable, how to use the MariaDB DATE_ADD function with the DAY_HOUR value in the EMPLOYEE table by the query. We hope that the concept of the MariaDB DATE_ADD function might be clear with the help of an example.

Read: MariaDB Check Constraint

MariaDB Date Add Days And Minute

In this section, we will learn how to use the MariaDB DATE_FUNCTION with the DAYS_MINUTE value in the parameter of the query. And it will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`,INTERVAL 5 DAY_MINUTE) AS DAYS_MINUTE FROM EMPLOYEE 
LIMIT 5;

As we see in the above query, we have used the DATE_ADD function on the CURRENT_DATE column from the EMPLOYEE table by using the SELECT statement. In the DATE_ADD function, we are adding 5 minutes of a day to every record of the CURRENT_DATE column.

To shorter the function name, we have used the alias clause with AS keyword and given the name as DAYS_MINUTE for the output column.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 5 records of the EMPLOYEE table by using the SELECT statement.

In this sub-topic, we have understood how to use the MariaDB DATE_ADD function with the DAY_MINUTE value in the EMPLOYEE table. We hope that concept might be clear with the help of an example.

Read: MariaDB Greatest Function

MariaDB Date Add Days Between Two Dates

In this section, we will learn how to use the MariaDB DATE_ADD function with the DATEDIFF function in the query. We will understand it with the help of an example.

In MariaDB, the DATEDIFF function returns days based on the difference between two date values from the query. The syntax of the MariaDB DATE_ADD function with the DATEDIFF function by the following query:

SYNTAX:

SELECT DATE_ADD(DATE, INTERVAL VALUE UNIT), DATEDIFF(DATE_1,DATE_2) FROM TABLE_NAME;

The syntax explanation:

• DATE_1, DATE_2: The two dates from which the difference is calculated. The calculation is DATE_1 – DATE_2.

Here is an illustrated example of the MariaDB DATE_ADD function with the DATEDIFF function by the following query:

EXAMPLE:

SELECT DATE_ADD(JOINING_DATE, INTERVAL 5 SECOND) AS DATE_SECOND, 
DATEDIFF(JOINING_DATE, EXPIRING_DATE) AS DATE_DIFFERENCE
FROM FAZZRO_COMPANY
LIMIT 4;

As we see in the above query, we have used the DATE_ADD function on the JOINING_DATE column and also used the DATEDIFF function on the JOINING_DATE and EXPIRING_DATE columns from the FAZZRO_COMPANY table by using the SELECT statement.

In the DATE_ADD function, we have added the 5 seconds in every record of the JOINING_DATE column. And in the DATEDIFF function, we have to calculate the difference days from the JOINING_DATE and EXPIRING_DATE columns.

We have also used the ALIAS clause for both the function to shorter the name and named DATE_SECOND and DATE_DIFFERENCE for the output column.

At the end of the query, we have used the LIMIT clause as LIMIT 5 to retrieve the first 4 records from the FAZZRO_COMPANY table by using the SELECT statement.

In this section, we have understood how to use the MariaDB DATE_ADD and DATEDIFF functions on the FAZZRO_COMPANY table. And we have explained with the help of a sample example.

Read: MariaDB Not Equal Operator

MariaDB Date Add Days Between

We will learn and understand how to use the MariaDB DATE_ADD function with the BETWEEN condition in the query. And it will be explained with the help of an illustrated example.

The MariaDB BETWEEN condition retrieves records based on the SELECT statement. It is also used in the UPDATE, DELETE and INSERT statements.

Let’s have a look at the MariaDB DATE_ADD function with the BETWEEN condition by the following query:

EXAMPLE:

SELECT DATE_ADD(JOINING_DATE, INTERVAL 10 DAY_HOUR) FROM FAZZRO_COMPANY
WHERE ID BETWEEN 5 AND 10;

• As we see in the above query, we have used the DATE_ADD function on the JOINING_DATE column from the FAZZO_COMPANY table by using the SELECT statement.
• In the DATE_ADD function, we have added the 10 hours of a day to all records of the JOINING_DATE column by using the INTERVAL keyword inside the parameter.
• In the WHERE condition, the ID column contains the value between 5 and 10. If the WHERE condition is TRUE, it will retrieve records from the FAZZRO_COMPANY table using the SELECT statement.

We have understood the concept of the MariaDB DATE_ADD function with the BETWEEN condition in the FAZZRO_COMPANY table. And we have explained it with the help of an example.

Read: MariaDB Drop Index + Examples

MariaDB Date Add Days Count

Here we will understand and learn how to use the MariaDB DATE_ADD function with the COUNT function in the query. And it will be explained with the help of an illustrated example.

In MariaDB, the COUNT function is used to return a total number of rows from the table in the query. Let’s see a sample example of the MariaDB DATE_ADD function with the COUNT function by the following query:

EXAMPLE:

SELECT DATE_ADD(JOINING_DATE, INTERVAL 10 DAY_HOUR) AS DATE_HOUR, 
COUNT(JOINING_DATE) AS TOTAL_COUNT FROM FAZZRO_COMPANY;

• In this query, we have used the DATE_ADD function and COUNT function on the JOINING_DATE column from the FAZZRO_COMPANY table by using the SELECT statement.
• In the DATE_ADD function, we have added 10 hours of a day by using the INTERVAL keyword. And in the COUNT function, we have counted the total number of rows from the JOINING_DATE column.
• To shorter the function name, we have used the ALIAS clause with AS keyword and given the name DATE_HOUR and TOTAL_COUNT for the output column. It is done by taking from the FAZZRO_COMPANY table by using the SELECT statement.

In this section, we have used an example to understand the concept of the MariaDB DATE_ADD and COUNT functions in the FAZZRO_COMPANY table.

Read: MariaDB Reserved Words

MariaDB Date Add Days Group By

In this tutorial, we will learn and understand how to use the MariaDB DATE_ADD function with the GROUP BY clause in the query. This is explained with the help of an illustrated example.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count(), min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups. For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

Here is a sample example of the MariaDB DATE_ADD function with the GROUP BY clause in the below following query:

EXAMPLE:

SELECT DATE_ADD(JOINING_DATE, INTERVAL 10 DAY_HOUR) AS DATE_HOUR, FULL_NAME
FROM FAZZRO_COMPANY
GROUP BY FULL_NAME;

In this query, we have used the DATE_ADD function on the JOINING_DATE column from the FAZZRO_COMPANY table by using the SELECT statement. In the DATE_ADD function, we have added 10 hours of a day by using the INTERVAL keyword.

We have also selected the records of the FULL_NAME column from the FAZZRO_COMPANY table by using the SELECT statement. At the end of the query, we grouped them by the FULL_NAME column by using the GROUP BY clause from the FAZZRO_COMPANY table.

In this section, we have used the simplest form of the MariaDB DATE_ADD function with the GROUP BY clause to add a date to the query. And we have used an example to make it understandable.

Read: MariaDB Rename View

MariaDB Date Add Days Greater Than

We’ll discuss and learn how to use the MariaDB DATE_ADD function with the GREATER THAN operator in the query. And we will try to explain it with the help of an illustrated example.

In MariaDB, the GREATER THAN operator is used after the WHERE condition which will retrieve records after selecting from the SELECT statement. It is also used in the UPDATE, DELETE and INSERT statements. The main use of the GREATER THAN operator is used to find greater value from numbers in the WHERE condition.

Here is an illustrated example of the MariaDB DATE_ADD function with the GREATER THAN operator by the following query:

EXAMPLE:

SELECT DATE_ADD(JOINING_DATE, INTERVAL 10 DAY_HOUR) AS DATE_HOUR, FULL_NAME
FROM FAZZRO_COMPANY
WHERE ID >5;

As we see in the preceding query, we have used the DATE_ADD function on the JOINING_DATE column from the FAZZRO_COMPANY table. And we have also selected the records of the FULL_NAME column from the table. In the DATE_ADD function, we are adding 10 hours of a day to every record of the JOINING_DATE column.

In the WHERE condition, we are taking the record of the ID column whose value is greater than 5. If the WHERE condition gets TRUE, it will retrieve all records from the FAZZRO_COMPANY table by using the SELECT statement.

In this section, we have understood the concept of the MariaDB DATE_ADD function with the GREATER THAN operator in the FAZZRO_COMPANY table with the help of a query. To make it easier to understand, we have used a sample example properly.

Read: MariaDB Date_Format

MariaDB Date Add Days Greater Than Today

In this section, we will understand and learn how to use the MariaDB DATE_ADD function with the GREATER THAN operator in the query. And we have also used the NOW() function in the WHERE condition. It is explained with the help of an illustrated example.

In MariaDB, the CURRENT_DATE function is used to return the current date or DateTime value in the query. Let’s see an example of the MariaDB DATE_ADD function with the GREATER THAN operator by the following query:

EXAMPLE:

SELECT DATE_ADD(CUSTOMER_DATE,INTERVAL 3 MONTH ),FIRST_NAME,LAST_NAME FROM WALMART_CUSTOMER
WHERE Customer_Date > CURRENT_DATE;

• As we see in the above query, we have used the DATE_ADD function on the CUSTOMER_DATE column of the WALMART_CUSTOMER table.
• We have selected all records of the FIRST_NAME and LAST_NAME columns of the WALMART_CUSTOMER table.
• In the DATE_ADD function, we have added the 3 MONTH by using the INTERVAL keyword in the CUSTOMER_DATE column from the WALMART_CUSTOMER table.
• In the WHERE condition, we have used the GREATER THAN operator on the CUSTOMER_DATE column and found the DATE values which are greater than the CURRENT_DATE column.
• If the WHERE condition gets TRUE, it will retrieve all records from the WALMART_CUSTOMER table using the SELECT statement.

In this section, we have understood how to use the CURRENT_DATE function with the DATE_ADD function in the query. We have also used an example to explain it in detail.

Read: MariaDB Rename Table

MariaDB Date Add Days Hours

Here we will understand and learn how to use the MariaDB DATE_ADD function with the HOUR unit in the query. And we will use an example to understand the detail.

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`, INTERVAL 5 HOUR) AS DATE_HOUR,FIRST_NAME,LAST_NAME
FROM EMPLOYEE;

In the preceding query, we used the DATE_ADD function on the CURRENT_DATE column from the EMPLOYEE table. And we have also selected the record of the FIRST_NAME and LAST_NAME columns from the EMPLOYEE table by using the SELECT statement.

In the DATE_ADD function, we have added the 5 hours to all records of the CURRENT_DATE column. And to shorter the function_name, we have used the ALIAS clause with AS keyword and given the name DATE_HOUR for the output column in the result set.

in this sub-topic, we have understood the concept of how to use the MariaDB DATE_ADD function with the HOUR unit in a query. And it is explained with the help of an example.

Read: MariaDB ENUM

MariaDB Date Add Days In Month

We will discuss and learn how to use the MariaDB DATE_ADD function with the MONTH unit in the query. And it will be explained with the help of an illustrated example.

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`, INTERVAL 5 MONTH) AS DATE_MONTH,FIRST_NAME,LAST_NAME
FROM EMPLOYEE;        

As we see in the above query, we have used the DATE_ADD function on the CURRENT_DATE column from the EMPLOYEE. We have also selected the records of the FIRST_NAME and LAST_NAME columns from the EMPLOYEE table by using the SELECT statement.

In the DATE_ADD function, we have added the 5 months of the unit in the records of the CURRENT_DATE column. To shorter the name of the function, we have used the ALIAS clause with AS keyword and we have given the name as DATE_MONTH for the output column in the result set.

NOTE:

In the query, we have a column_name as CURRENT_DATE which is reserved words of the MariaDB. To use column_name as RESERVED WORDS, we have use reserve back slash sign as ` `.

We have used an example to make it understandable how to use the MariaDB DATE_ADD function with the MONTH unit in the EMPLOYEE table by the query.

Read: MariaDB vs Postgres

MariaDB Date Add Days Like

We’ll learn how to use the MariaDB DATE_ADD function in conjunction with the LIKE clause in a query. We’ll give an illustrated example to help you understand.

Wildcards can be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE query with the MariaDB LIKE condition. This gives us the ability to match patterns. Let’s use an example of the MariaDB DATE_ADD function with the LIKE clause by the following query:

EXAMPLE:

SELECT DATE_ADD(`CURRENT_DATE`, INTERVAL 5 MICROSECOND) AS DATE_MICROSECOND,
FIRST_NAME,LAST_NAME
FROM EMPLOYEE
WHERE FIRST_NAME LIKE '%a';       

• We have used the DATE_ADD function on the CURRENT_DATE column in the preceding query in the EMPLOYEE table.
• We selected records of the FIRST_NAME and LAST_NAME columns from the EMPLOYEE table by using the SELECT statement.
• In the DATE_ADD function, we have added the 5 microseconds to all records of the CURRENT_DATE column.
• In the WHERE condition, the FIRST_NAME column will retrieve all names whose end with the alphabet a which is lower_case. It is used by the LIKE clause as FIRST_NAME LIKE ‘%a’. If the WHERE condition gets TRUE, it will retrieve all records from the EMPLOYEE table using the SELECT statement.
• We have also used the ALIAS clause with AS keyword to shorter the function_name and given the name DATE_MICROSECOND for the output column in the result set.

We hope that the concept of how to use the MariaDB DATE_ADD function with the LIKE clause was explained with the help of an example.

Read: MariaDB Variables Tutorial

MariaDB Date Add Days Not Working

We will understand why the MariaDB DATE_ADD function is not working in a query. It will be easy to explain it with the help of an example.

In MariaDB documentation, query error usually arises due to syntax or logical errors. Suppose we use the reserved words in the CURRENT_DATE column of the EMPLOYEE table. First, we will change the column_name from CURRENT_DATE to DATE column of the EMPLOYEE table by using the ALTER TABLE statement:

ALTER TABLE EMPLOYEE CHANGE COLUMN `CURRENT_DATE`  DATE datetime;

The MariaDB ALTER TABLE statement has been used to change the column of the CURRENT_DATE column to the DATE column with the DATETIME data type.

Here is a scenario of the MariaDB DATE_ADD function not working in a query. Let’s have a look at the error example of the MariaDB DATE_ADD function by the following query:

ERROR EXAMPLE:

UPDATE EMPLOYEE
SET DATE = DATE + 27
WHERE FIRST_NAME = 'Russ';

SELECT * FROM EMPLOYEE
WHERE FIRST_NAME = 'Russ';

In this query, we have added the value of the DATE column by 27 in the EMPLOYEE table on the name of RUSS in the FIRST_NAME column. But if we check it by using the SELECT statement on the EMPLOYEE table, the value of the date as 27 has been counted in seconds. And it has been added to the FIRST_NAME column of RUSS.

ERROR REMOVED EXAMPLE:

UPDATE EMPLOYEE 
SET `DATE` = DATE_ADD(`DATE`, INTERVAL 27 DAY)
WHERE FIRST_NAME='Russ';

SELECT * FROM EMPLOYEE
WHERE FIRST_NAME ='RUSS';

In the first query, we have updated the DATE column by 27 days in the DATE_ADD function of the EMPLOYEE table which is based on the WHERE condition, In the WHERE condition, if the FIRST_NAME column is equal to RUSS then the value will be added to 27 days in the DATE column of the EMPLOYEE table.

If we want to check it then we can use the SELECT statement to retrieve all records of the EMPLOYEE table based on the WHERE condition. In the WHERE condition, if the FIRST_NAME is equal to RUSS. Then it will retrieve all records of the EMPLOYEE table by using the SELECT statement.