DATE() and DATE_ADD() Function in MariaDB

1. DATE() Function :

In MariaDB, the DATE() function is used to return extracts the date value from a date or datetime expression. In this function, the first parameter will be a date or DateTime. This function will return extracts the date value from the expression. If the expression is not date or DateTime then this function will return the NULL Value.

Syntax :

DATE(expression)

Parameter :

• Expression – date or DateTime value.

Returns : The date value from datetime.

Example-1 :

SELECT DATE('2020-09-11 11:13:19');

Output :

'2020-09-11'

Example-2 :

SELECT DATE('2020-04-12 11:13:01.000001');

Output :

'2020-04-12'

Example-3 :

SELECT DATE('Today is 2020-10-17');

Output :

NULL

2. DATE_ADD() Function :

In MariaDB, the DATE_ADD() Function is used to return the time/DateTime value after which a certain date /timeinterval has been added. In this function, the first parameter will be a start_value and the second parameter will be the interval value. This function will return DateTime with the adding given interval. This function works similar to the DATE_SUB function. If the given interval is negative.If the interval value that is too short for the unit then DATE_ADD function will assume that the left-most portion of the interval value was not provided.

Syntax :

DATE_ADD(date, INTERVAL value unit)

Parameters :

• Date – Date to which the interval should be added.
• Days – Number of days to add to date.
• value – The time/date interval that you wish to add.
• unit – The unit type of the interval such as DAY, MONTH, MINUTE, HOUR, etc.

Returns : Time/DateTime value after which a certain date interval has been added.

Example-1 :

SELECT DATE_ADD('2020-01-10 08:44:21', INTERVAL 25 MINUTE);

Output :

'2020-01-10 09:09:21'

Example-2 :

SELECT DATE_ADD('2014-05-17 08:44:21.000001', INTERVAL '7 1:03:12.000001' DAY_MICROSECOND);

Output :

'2014-05-24 09:47:33.000002'

Example-3 :

SELECT DATE_ADD('2019-07-10', INTERVAL '1-2' YEAR_MONTH);

Output :

'2020-09-10'