MariaDB Date Function with Examples

In this MariaDB tutorial, we will learn about MariaDB Date Function. Additionally, we will cover the following topics.

  • MariaDB Date Functions
  • MariaDB Add Date Function
  • MariaDB Current Date Function
  • MariaDB CURDATE Function
  • MariaDB DATEDIFF Function
  • MariaDB TIMESTAMPDIFF Function
  • MariaDB DAY Function
  • MariaDB DATE_ADD Function
  • MariaDB DATE_SUB Function
  • MariaDB DATE_FORMAT Function
  • MariaDB DAYNAME Function
  • MariaDB DAYOFWEEK Function
  • MariaDB LAST_DAY Function
  • MariaDB NOW Function
  • MariaDB MONTH Function
  • MariaDB YEAR Function
  • MariaDB TIMEDIFF Function
  • MariaDB STR_TO_DATE Function

MariaDB Date Functions

Here, we will learn about the MariaDB Date Functions and some common date functions used in MariaDB.

The MariaDB Date Functions is used to extract the Date value from the Date/ DateTime expression. And also, it performs various operations on Date/DateTime expressions like calculating the difference between two different Date/DateTime expressions, and adding or subtracting an interval to a date.

Some of the most commonly used MariaDB Date Functions are given below.

  • CURDATE() – returns the current date on the server on which the MariaDB server is presented.
  • DATEDIFF() – returns the difference between the two dates or DateTime expressions.
  • DAY() – returns the day of the month from a date or DateTime expression.
  • DATE_ADD() – adds an interval to a date.
  • DATE_SUB() – subtracts an interval to a date.
  • DATE_FORMAT() – returns a specific date format.
  • DAYNAME() – returns the weekday of a date.
  • DAYOFWEEK() – returns the weekday index of a date.
  • LASTDAY() – returns on the last day of a month
  • NOW() – returns the current date and time.
  • MONTH() – returns the month of a date in integer.
  • STR_TO_DATE() – returns a date converted from a string.
  • TIMEDIFF() – returns the difference between two-time values or DateTime values.
  • TIMESTAMPDIFF() – returns between two different timestamps.
  • YEAR() – returns the year of a date.

Read MariaDB DateTime Tutorial

MariaDB Add Date Function

Here, we are going to learn about the MariaDB Add Date Function, and its syntax with examples.

MariaDB ADDDATE() Function returns a date after adding an interval of date or time to date.

The syntax for the ADDDATE() Function in MariaDB is given below.

ADDDATE( date, INTERVAL value unit )
ADDDATE(expr,days)

Here, the parameters and arguments are given below.

  • date – the date to which interval will be added.
  • value – the amount of interval to be added to the date.
  • unit – it is the type of interval that will be added i.e. seconds, minutes, hours, days, months, or years. Other possible types of intervals are included in this list.
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • DAY_MICROSECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • HOUR_MICROSECOND
  • MINUTE_SECOND
  • MINUTE_MICROSECOND
  • SECOND_MICROSECOND
  • YEAR
  • QUARTER
  • MONTH
  • WEEK
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • MICROSECOND

Several examples of MariaDB ADDDATE() Function with every interval unit are given below.

Example 1 – The MariaDB ADDDATE() Function with interval unit as MICROSECOND.

SELECT ADDDATE('2004-06-19 09:46:27.000009', INTERVAL 8 MICROSECOND);

In the above example, we are using the ADDDATE function to add 8 milliseconds to 2004-06-19 09:46:27.000009. And after addition, we will get the following result.

MariaDB ADDDATE Microsecond
MariaDB ADDDATE Microsecond

Example 2 – The MariaDB ADDDATE() Function with interval unit as SECOND.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL -39 SECOND);

In the example, we are using the ADDDATE function to subtract 39 seconds from the 2004-06-19 09:46:27 DateTime value. In the end, we will get the following result.

MariaDB ADDDATE Second
MariaDB ADDDATE Second

Read MariaDB IF Tutorial

Example 3 – The MariaDB ADDDATE() Function with interval unit as MINUTE.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL 45 MINUTE);

In the example, we are using the ADDDATE function to add 45 minutes to the 2004-06-19 09:46:27 DateTime value. In the last, we will get the following result.

MariaDB ADDDATE Minute
MariaDB ADDDATE Minute

Example 4 – The MariaDB ADDDATE() Function with interval unit as HOUR.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL -8 HOUR);

In this example, we are using the ADDDATE function to subtract 8 hours from the 2004-06-19 09:46:27 DateTime value. As a result, we will get the following result.

MariaDB ADDDATE Hour
MariaDB ADDDATE Hour

Example 5 – The MariaDB ADDDATE() Function with interval unit as DAY.

SELECT ADDDATE('2004-06-19', INTERVAL 19 DAY);

In the above example, we are using the ADDDATE function to add 19 days into the 2004-06-19 date value. And in the last, we will get the following date result.

MariaDB ADDDATE Day
MariaDB ADDDATE Day

Example 6 – The MariaDB ADDDATE() Function with interval unit as WEEK.

SELECT ADDDATE('2004-06-19', INTERVAL 22 WEEK);

In the example, we are adding 22 weeks into the 2004-06-19 date value. And it will return the date value after 22 weeks.

MariaDB ADDDATE Week
MariaDB ADDDATE Week

Read MariaDB Date

Example 7 – The MariaDB ADDDATE() Function with interval unit as MONTH.

SELECT ADDDATE('2004-06-19', INTERVAL -9 MONTH);

In the example, we are subtracting 9 months from the 2004-06-19 date value. In the end, we will get 9 months earlier date value.

MariaDB ADDDATE Month
MariaDB ADDDATE Month

Example 8 – The MariaDB ADDDATE() Function with interval unit as QUARTER.

SELECT ADDDATE('2004-06-19', INTERVAL 5 QUARTER);

In the example, we are using the ADDDATE function to add 5 quarter value to the 2004-06-19 date value. And we will get the date value after 5 quarter.

MariaDB ADDDATE Quarter
MariaDB ADDDATE Quarter

Example 9 – The MariaDB ADDDATE() Function with interval unit as YEAR.

SELECT ADDDATE('2004-06-19', INTERVAL 17 YEAR);

In the example, we are using the ADDDATE function to add 17 into the year part of the 2004-06-19 date value. So, we will get the exact date value after 17 years.

MariaDB ADDDATE Year
MariaDB ADDDATE Year

Example 10 – The MariaDB ADDDATE() Function with interval unit as SECOND_MICROSECOND.

SELECT ADDDATE('2004-06-19 09:49:27.0000506', INTERVAL '52.0000321' SECOND_MICROSECOND);

In the above example, we are adding the second and millisecond value to the 2004-06-19 09:49:27.0000506 DateTime value.

MariaDB ADDDATE SECOND_MICROSECOND
MariaDB ADDDATE SECOND_MICROSECOND

Read MariaDB DATEDIFF Function

Example 11 – The MariaDB ADDDATE() Function with interval unit as MINUTE_MICROSECOND.

SELECT ADDDATE('2004-06-19 09:49:27.000506', INTERVAL '6:14.000501' MINUTE_MICROSECOND);

In the example, we are adding the

MariaDB ADDDATE MINUTE_MICROSECOND
MariaDB ADDDATE MINUTE_MICROSECOND

Example 12 – The MariaDB ADDDATE() Function with interval unit as MINUTE_SECOND.

SELECT ADDDATE('2004-06-19 09:49:27', INTERVAL '7:42' MINUTE_SECOND);
MariaDB ADDDATE MINUTE_SECOND
MariaDB ADDDATE MINUTE_SECOND

Example 13 – The MariaDB ADDDATE() Function with interval unit as HOUR_MICROSECOND.

SELECT ADDDATE('2004-06-19 09:49:27.000701', INTERVAL '7:43:52.000901' HOUR_MICROSECOND);
MariaDB ADDDATE HOUR_MICROSECOND
MariaDB ADDDATE HOUR_MICROSECOND

Read MariaDB Create Sequence

Example 14 – The MariaDB ADDDATE() Function with interval unit as HOUR_SECOND.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL '5:53:32' HOUR_SECOND);
MariaDB ADDDATE HOUR_SECOND
MariaDB ADDDATE HOUR_SECOND

Example 15 – The MariaDB ADDDATE() Function with interval unit as HOUR_MINUTE.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL '11:43' HOUR_MINUTE);
MariaDB ADDDATE HOUR_MINUTE
MariaDB ADDDATE HOUR_MINUTE

Example 16 – The MariaDB ADDDATE() Function with interval unit as DAY_MICROSECOND.

SELECT ADDDATE('2004-06-19 09:46:27.000501', INTERVAL '8 9:13:52.000871' DAY_MICROSECOND);
MariaDB ADDDATE DAY_MICROSECOND
MariaDB ADDDATE DAY_MICROSECOND

Example 17 – The MariaDB ADDDATE() Function with interval unit as DAY_SECOND.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL '15 12:43:22' DAY_SECOND);
MariaDB ADDDATE DAY_SECOND
MariaDB ADDDATE DAY_SECOND

Example 18 – The MariaDB ADDDATE() Function with interval unit as DAY_MINUTE.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL '32 11:43' DAY_MINUTE);
MariaDB ADDDATE DAY_MINUTE
MariaDB ADDDATE DAY_MINUTE

Read MariaDB AUTO_INCREMENT

Example 19 – The MariaDB ADDDATE() Function with interval unit as DAY_HOUR.

SELECT ADDDATE('2004-06-19 09:46:27', INTERVAL '56 4' DAY_HOUR);
MariaDB ADDDATE DAY_HOUR
MariaDB ADDDATE DAY_HOUR

Example 20 – The MariaDB ADDDATE() Function with interval unit as YEAR_MONTH.

SELECT ADDDATE('2004-06-19', INTERVAL '17-8' YEAR_MONTH);
MariaDB ADDDATE YEAR_MONTH
MariaDB ADDDATE YEAR_MONTH

Example 21 – Example for another syntax of the MariaDB ADDDATE() Function.

SELECT ADDDATE('2015-09-28', 2);
MariaDB ADDDATE Day Only
MariaDB ADDDATE Day

This is how we can implement MariaDB ADDDATE Function.

Read MariaDB Primary Key

MariaDB Current Date Function

Here, we are going to learn about the MariaDB Current Date Function, and its syntax with examples.

MariaDB CURRENT_DATE() Function returns the current date value.

The syntax for the CURRENT_DATE() Function in MariaDB is given below.

CURRENT_DATE()

In the syntax of the MariaDB Current Date function, there are no parameters or arguments.

It returns the current date in ‘YYYY-MM-DD’ format if used in a string context and YYYYMMDD format if used in a numeric context in MariaDB.

Several examples for the Current Date Function in MariaDB are given below.

SELECT CURRENT_DATE();
MariaDB CURRENT DATE Function
MariaDB CURRENT DATE Function

Here, the result is displayed in the String Context by default.

SELECT CURRENT_DATE() + 0;
MariaDB CURRENT DATE Function In Numeric
MariaDB CURRENT DATE Function In Numeric

Here, the result is displayed in a Numeric context just by adding zero to it.

SELECT CURRENT_DATE() + 1;

Here, we are displaying the result in numeric as well as we are adding one to the current date.

MariaDB CURRENT DATE Plus One
MariaDB CURRENT DATE Plus One
SELECT CURRENT_DATE() + INTERVAL 6 MONTH;
MariaDB Adding To The CURRENT DATE Function
MariaDB Adding To The CURRENT DATE Function

We can also add intervals to the Current Date Function.

This is how we can implement MariaDB Current Date Function.

Read MariaDB Delete Row

MariaDB CURDATE Function

Here, we are going to learn about the MariaDB CURDATE() Function and its syntax with examples.

MariaDB CURDATE() Function returns the current date and it is the synonym of the CURRENT_DATE() Function.

The syntax for the CURDATE() Function in MariaDB is given below.

CURDATE()

In the syntax of the MariaDB CURDATE function, there are no parameters or arguments.

It returns the current date in ‘YYYY-MM-DD’ format if used in a string context and YYYYMMDD format if used in a numeric context in MariaDB.

Several examples of the CURDATE Function in MariaDB are given below.

SELECT CURDATE();
MariaDB CURDATE Function
MariaDB CURDATE Function

Here, the result is displayed in the String context by default.

SELECT CURDATE() + 0;
MariaDB CURDATE Function In Numeric
MariaDB CURDATE Function In Numeric

Here, the result is displayed in a Numeric context just by adding zero to it.

SELECT CURDATE() + 1;
MariaDB CURDATE Plus One
MariaDB CURDATE Plus One

Here, we are displaying the result in numeric as well as we are adding one to the current date.

SELECT CURDATE() + INTERVAL 6 MONTH;
MariaDB Adding To The CURDATE Function
MariaDB Adding To The CURDATE Function

We can also add intervals to the Current Date Function in MariaDB. In the above example, we added a 6-month interval value to the current date value.

Read MariaDB Full Outer Join

MariaDB DATEDIFF Function

Here, we will learn about MariaDB DATEDIFF Function with its syntax and example.

The MariaDB DATEDIFF Function simply returns the difference in days between two different dates. It simply takes two dates as parameters and calculates the difference between them.

The syntax for the DATEDIFF Function in MariaDB is given below.

DATEDIFF( date1, date2 )

Here, date1 and date2 is the parameter whose values are calculated using the DATEDIFF function in MariaDB.

The calculation is done like this: date1 – date2

Several examples of the DATEDIFF function in MariaDB are given below.

SELECT DATEDIFF('2019-09-26', '2018-05-17');
MariaDB DATEDIFF
MariaDB DATEDIFF
SELECT DATEDIFF('2011-07-16 11:34:44', '2013-03-24');
MariaDB DATEDIFF Example
MariaDB DATEDIFF
SELECT DATEDIFF('2004-06-29', '2008-11-21');
Example of MariaDB DATEDIFF
MariaDB DATEDIFF
SELECT DATEDIFF('2008-11-21', '2004-06-29');
MariaDB DATEDIFF Example
MariaDB DATEDIFF

We can also calculate the days between a particular date and the current date using the CURDATE() function which returns the current date.

To find the days between any particular date (be it 2004-06-29 ), type the following command.

SELECT DATEDIFF(CURDATE(), '2004-06-29');

In the above example, we are calculating the Date difference between the current date value and 2004-06-29. Note that the date format is not of the United States of America. It is in ‘YYY-MM-DD‘ format.

MariaDB DATEDIFF CURDATE
MariaDB DATEDIFF CURDATE()

This is how the MariaDB DATEDIFF function works.

Read MariaDB Cast with Examples

MariaDB TIMESTAMPDIFF Function

Here, we will learn about MariaDB TIMESTAMPDIFF Function, its syntax, and an example.

MariaDB TIMESTAMPDIFF Function is a built-in function that returns the difference between two dates or DateTime expression.

The syntax for the TIMESTAMPDIFF Function in MariaDB is given below.

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

Here,

unit – It is the unit in which the difference between two dates is needed to be calculated. The units in which we can calculate the difference are given below.

  • YEAR
  • QUARTER
  • MONTH
  • WEEK
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • MICROSECOND

datetime_expr1, datetime_expr2 – It is the parameter in which we enter the two different DateTime expressions.

Several examples of the TIMESTAMPDIFF function in MariaDB.

Example 1 – TIMESTAMPDIFF function MariaDB

SELECT TIMESTAMPDIFF(DAY, '2013-06-07', '2030-08-08');

Here, we are calculating the days between two different DateTime expressions. And we get 6271 as result.

MariaDB TIMESTAMPDIFF
MariaDB TIMESTAMPDIFF

Example 2 – Negative value in TIMESTAMPDIFF in MariaDB.

SELECT TIMESTAMPDIFF(DAY, '2030-08-08', '2013-06-07');
MariaDB TIMESTAMPDIFF Negative Value
MariaDB TIMESTAMPDIFF Negative Value

Here, we are calculating the days between two different DateTime expressions and having negative results.

Example 3 – MariaDB TIMESTAMPDIFF in Hours.

SELECT TIMESTAMPDIFF(
    HOUR, 
    '2013-02-04 00:00:00', 
    '2013-02-01 12:30:45'
    ) 
AS Result;
MariaDB TIMESTAMPDIFF in Hours
MariaDB TIMESTAMPDIFF in Hours

Here, we are calculating the number of hours between two different DateTime expressions.

Read MariaDB Substring

Example 4 – MariaDB TIMESTAMPDIFF in Hours with mixed DateTime expressions.

SELECT TIMESTAMPDIFF(
    HOUR, 
    '2013-02-04', 
    '2013-02-01 12:30:45'
    ) 
AS Result;
MariaDB TIMESTAMPDIFF in Hours with Mixed Expressions
MariaDB TIMESTAMPDIFF in Hours with Mixed Expressions

Here, we are calculating the no. of hours between two different DateTime expressions.

Example 5 – MariaDB TIMESTAMPDIFF in Microseconds.

SELECT TIMESTAMPDIFF(
    MICROSECOND, 
    '2013-02-04 10:30:45.000000',
    '2013-02-01 10:30:45.123456'
    )
AS Result;
MariaDB TIMESTAMPDIFF in MICROSECOND
MariaDB TIMESTAMPDIFF in Microseconds

Here, we are calculating the microseconds between two different DateTime expressions.

Example 6 – MariaDB TIMESTAMPDIFF in Microseconds without specifying in DateTime expression.

SELECT TIMESTAMPDIFF(
    MICROSECOND, 
    '2013-02-01 10:30:45',
    '2013-02-01 12:40:45'
    )
AS Result;
MariaDB TIMESTAMPDIFF in MICROSECONDS
MariaDB TIMESTAMPDIFF in Microseconds

Here, we are calculating the microseconds between two different DateTime expressions without specifying in DateTime expression.

Example 7 – MariaDB TIMESTAMPDIFF In Days using Current Date Function.

SELECT 
    NOW(),
    TIMESTAMPDIFF(DAY, '2018-03-31', NOW()) AS Diff;
MariaDB TIMESTAMPDIFF In Days From Current date
MariaDB TIMESTAMPDIFF In Days From Current date

Here, we are calculating the days from the current date to different DateTime expressions.

Read MariaDB LIMIT

Example 8 – MariaDB TIMESTAMPDIFF NULL.

SELECT TIMESTAMPDIFF(
    YEAR, 
    '2015-12-21',
    NULL
    )
AS Result;
MariaDB TIMESTAMPDIFF NULL
MariaDB TIMESTAMPDIFF NULL

Here, we are calculating the days from NULL value to different DateTime expressions.

This is how we can implement MariaDB TIMESTAMPDIFF Function.

Read MariaDB index with Examples

MariaDB DAY Function

Here, we will learn about MariaDB DAY Function, its syntax, and an example.

MariaDB DAY Function returns the day part of a date in MariaDB. It returns the value in the range of 1 to 31 and it is the synonym of the DAYOFMONTH function.

The syntax of Day Function in MariaDB is given below.

DAY( date_value )

Here, date_value is the Date or DateTime expression for extracting the day.

Several examples of DAY() Function in MariaDB are given below.

Example 1 – MariaDB DAY() Function to extract from Date expression.

SELECT DAY('2014-05-19');
MariaDB DAY Function
MariaDB DAY Function

Example 2 – MariaDB DAY() Function to extract from DateTime expression.

SELECT DAY('2022-01-29 09:58:35');
DAY Function in MariaDB
DAY Function in MariaDB

Example 2 – MariaDB DAY() Function using CURDATE() function.

SELECT DAY(CURDATE());
MariaDB DAY Function using CURDATE function
MariaDB DAY Function using CURDATE function

This is how we can implement MariaDB CURDATE Function.

Read MariaDB varchar

MariaDB DATE_ADD Function

Here, we are going to learn about the MariaDB DATE_ADD Function, and its syntax with examples.

MariaDB DATE_ADD() Function returns a date after adding an interval of date or time to date.

The syntax for the DATE_ADD() Function is given below.

DATE_ADD( date, INTERVAL value unit )

Here, the parameters and arguments are given below.

  • date – the date to which interval will be added.
  • value – the amount of interval to be added to the date.
  • unit – it is the type of interval that will be added i.e. seconds, minutes, hours, days, months, or years. Other possible types of intervals are included in this list.
  1. YEAR_MONTH
  2. DAY_HOUR
  3. DAY_MINUTE
  4. DAY_SECOND
  5. DAY_MICROSECOND
  6. HOUR_MINUTE
  7. HOUR_SECOND
  8. HOUR_MICROSECOND
  9. MINUTE_SECOND
  10. MINUTE_MICROSECOND
  11. SECOND_MICROSECOND
  12. YEAR
  13. QUARTER
  14. MONTH
  15. WEEK
  16. DAY
  17. HOUR
  18. MINUTE
  19. SECOND
  20. MICROSECOND

Several examples of the MariaDB DATE_ADD() Function with every interval unit are given below.

Example 1 – The MariaDB DATE_ADD() Function with interval unit as MICROSECOND.

SELECT DATE_ADD('2004-06-19 09:46:27.000009', INTERVAL 8 MICROSECOND);
MariaDB DATE_ADD Microsecond
MariaDB DATE_ADD Microsecond

Example 2 – The MariaDB DATE_ADD() Function with interval unit as SECOND.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL -39 SECOND);
MariaDB DATE_ADD Second
MariaDB DATE_ADD Second

Example 3 – The MariaDB DATE_ADD() Function with interval unit as MINUTE.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL 45 MINUTE);
MariaDB DATE_ADD Minute
MariaDB DATE_ADD Minute

Example 4 – The MariaDB DATE_ADD() Function with interval unit as HOUR.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL -8 HOUR);
MariaDB DATE_ADD Hour
MariaDB DATE_ADD Hour

Example 5 – The MariaDB DATE_ADD() Function with interval unit as DAY.

SELECT DATE_ADD('2004-06-19', INTERVAL 19 DAY);
MariaDB DATE_ADD Day
MariaDB DATE_ADD Day

Read MariaDB LIKE Operator

Example 6 – The MariaDB DATE_ADD() Function with interval unit as WEEK.

SELECT DATE_ADD('2004-06-19', INTERVAL 22 WEEK);
MariaDB DATE_ADD Week
MariaDB DATE_ADD Week

Example 7 – The MariaDB DATE_ADD() Function with interval unit as MONTH.

SELECT DATE_ADD('2004-06-19', INTERVAL -9 MONTH);
MariaDB ADD_DATE Month
MariaDB DATE_ADD Month

Example 8 – The MariaDB DATE_ADD() Function with interval unit as QUARTER.

SELECT DATE_ADD('2004-06-19', INTERVAL 5 QUARTER);
MariaDB DATE_ADD Quarter
MariaDB DATE_ADD Quarter

Example 9 – The MariaDB DATE_ADD() Function with interval unit as YEAR.

SELECT DATE_ADD('2004-06-19', INTERVAL 17 YEAR);
MariaDB DATE_ADD Year
MariaDB DATE_ADD Year

Example 10 – The MariaDB DATE_ADD() Function with interval unit as SECOND_MICROSECOND.

SELECT DATE_ADD('2004-06-19 09:49:27.0000506', INTERVAL '52.0000321' SECOND_MICROSECOND);
MariaDB DATE_ADD Second_Microsecond
MariaDB DATE_ADD Second_Microsecond

Read MariaDB query examples

Example 11 – The MariaDB DATE_ADD() Function with interval unit as MINUTE_MICROSECOND.

SELECT DATE_ADD('2004-06-19 09:49:27.000506', INTERVAL '6:14.000501' MINUTE_MICROSECOND);
MariaDB DATE_ADD Minute_Microsecond
MariaDB DATE_ADD Minute_Microsecond

Example 12 – The MariaDB DATE_ADD() Function with interval unit as MINUTE_SECOND.

SELECT DATE_ADD('2004-06-19 09:49:27', INTERVAL '7:42' MINUTE_SECOND);
MariaDB DATE_ADD Minute_Second
MariaDB DATE_ADD Minute_Second

Example 13 – The MariaDB DATE_ADD() Function with interval unit as HOUR_MICROSECOND.

SELECT DATE_ADD('2004-06-19 09:49:27.000701', INTERVAL '7:43:52.000901' HOUR_MICROSECOND);
MariaDB DATE_ADD Hour_Microsecond
MariaDB DATE_ADD Hour_Microsecond

Example 14 – The MariaDB DATE_ADD() Function with interval unit as HOUR_SECOND.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL '5:53:32' HOUR_SECOND);
MariaDB DATE_ADD Hour_Second
MariaDB DATE_ADD Hour_Second

Read How to create a database from an SQL file in MariaDB

Example 15 – The MariaDB DATE_ADD() Function with interval unit as HOUR_MINUTE.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL '11:43' HOUR_MINUTE);
MariaDB DATE_ADD Hour_Minute
MariaDB DATE_ADD Hour_Minute

Example 16 – The MariaDB DATE_ADD() Function with interval unit as DAY_MICROSECOND.

SELECT DATE_ADD('2004-06-19 09:46:27.000501', INTERVAL '8 9:13:52.000871' DAY_MICROSECOND);

Example 17 – The MariaDB DATE_ADD() Function with interval unit as DAY_SECOND.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL '15 12:43:22' DAY_SECOND);
MariaDB DATE_ADD Day_Second
MariaDB DATE_ADD Day_Second

Example 18 – The MariaDB DATE_ADD() Function with interval unit as DAY_MINUTE.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL '32 11:43' DAY_MINUTE);

Example 19 – The MariaDB DATE_ADD() Function with interval unit as DAY_HOUR.

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL '56 4' DAY_HOUR);
MariaDB DATE_ADD Day_Hour
MariaDB DATE_ADD Day_Hour

Example 20 – The MariaDB DATE_ADD() Function with interval unit as YEAR_MONTH.

SELECT DATE_ADD('2004-06-19', INTERVAL '17-8' YEAR_MONTH);
MariaDB DATE_ADD Year_Month
MariaDB DATE_ADD Year_Month

This is how we can implement the MariaDB DATE_ADD Function.

Read MariaDB Insert Into

MariaDB DATE_SUB Function

Here, we are going to learn about the MariaDB DATE_SUB Function, and its syntax with examples.

MariaDB DATE_SUB() Function returns a date after subtracting an interval of date or time to date.

The syntax for the DATE_SUB() Function in MariaDB is given below.

DATE_SUB( date, INTERVAL value unit )

Here, the parameters and arguments are given below.

  • date – the date to which interval will be subtracted.
  • value – the amount of interval to be subtracted in the date.
  • unit – it is the type of interval that will be subtracted i.e. seconds, minutes, hours, days, months, or years. Other possible types of intervals are included in this list.
  • YEAR_MONTH
  • DAY_HOUR
  • DAY_MINUTE
  • DAY_SECOND
  • DAY_MICROSECOND
  • HOUR_MINUTE
  • HOUR_SECOND
  • HOUR_MICROSECOND
  • MINUTE_SECOND
  • MINUTE_MICROSECOND
  • SECOND_MICROSECOND
  • YEAR
  • QUARTER
  • MONTH
  • WEEK
  • DAY
  • HOUR
  • MINUTE
  • SECOND
  • MICROSECOND

An example for MariaDB DATE_SUB() Function with Microsecond as an interval unit is given below.

SELECT DATE_SUB('2004-06-19 09:46:27.000009', INTERVAL 8 MICROSECOND);
MariaDB DATE_SUB Microsecond
MariaDB DATE_SUB Microsecond

The rest of the examples with other units of the interval can be implemented as DATE_ADD by simply replacing ‘DATE_ADD’ with ‘DATE_SUB’.

This is how we can implement MariaDB DATE_SUB Function.

Read MariaDB create procedure

MariaDB DATE_FORMAT Function

Here, we are going to learn about the MariaDB DATE_FORMAT Function, and its syntax with examples.

MariaDB DATE_FORMAT() Function returns a date specified by a format mask.

The syntax for the DATE_FORMAT() Function is given below.

DATE_FORMAT( date, format_mask )

Here, the parameters and arguments are given below.

  • date – the date which will be formatted.
  • format_mask – the format that we want to apply on a date and below we have a list of options for format_mask which can also be used in combination.
ValueDescription
%aName of weekdays abbreviated (Sun to Sat)
%bName of Months abbreviated (Jan to Dec)
%cMonth as a numeric value from 0 to 12
%DDay of the month as a numeric value followed
by a suffix like 1st, 2nd, 3rd, and so on
%dDay of the month as a numeric value from 01 to 31
%eDay of the month as a numeric value from 00 to 31
%fMicroseconds ranging from 000000 to 999999
%HAn hour from 00 to 23
%hAn hour from 00 to 12
%iMinutes from 00 to 59
%jDay of the year ranging from 001 to 366
%lAn hour from 1 to 12
%MMonth name in full from January to December
%mMonth name as a numeric value from 00 to 12
%pAM or PM
%rTime in 12 hour AM or PM format as ‘hh:mm:ss AM/PM’
%SSeconds ranging from 00 to 59
%TTime in 24-hour format as ‘hh:mm:ss’
%UThe week where Sunday is the first day of the week (00 to 53)
%uThe week where Monday is the first day of the week (00 to 53)
%VThe week where Sunday is the first day of the week (01 to 53)
%vThe week where Monday is the first day of the week (01 to 53)
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday=0 and Saturday=6
%XYear for the week where Sunday is the first day of the week
%xYear for the week where Monday is the first day of the week
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value

Read How to Change Column in MariaDB

Several examples of MariaDB DATE_FORMAT() Function with every format_mask option are given below.

Example 1 – The MariaDB DATE_FORMAT() Function displays year only.

SELECT DATE_FORMAT('2004-06-28', '%Y');
MariaDB DATE_FORMAT Year
MariaDB DATE_FORMAT Year

Example 2 – The MariaDB DATE_FORMAT() Function displays the month, day(0 to 31), and year.

SELECT DATE_FORMAT('2004-06-28', '%M %e %Y');
MariaDB DATE_FORMAT With Combinations
MariaDB DATE_FORMAT With Combinations

Example 3 – The MariaDB DATE_FORMAT() Function displays the month, day(01 to 31), and year.

SELECT DATE_FORMAT('2004-06-28', '%M %d, %Y');
Combination in MariaDB DATE_FORMAT
Combination in MariaDB DATE_FORMAT

This is how we can implement the MariaDB DATE_FORMAT Function.

Read MariaDB Update Statement

MariaDB DAYNAME Function

Here, we are going to learn about the MariaDB DAYNAME Function, and its syntax with examples.

MariaDB DAYNAME() Function returns the name of weekdays corresponding to a particular Date.

The syntax for the DAYNAME() Function in MariaDB is given below.

DAYNAME( date_value )

Here, date_value is the Date or DateTime expression from which the weekdays name will be extracted.

Several examples for the DAYNAME() Function in MariaDB are given below.

Example 1 – MariaDB DAYNAME() Function in Date expression.

SELECT DAYNAME('2004-06-21');
MariaDB DAYNAME Function
MariaDB DAYNAME Function

Example 2 – MariaDB DAYNAME() Function in DateTime expression.

SELECT DAYNAME('2004-06-11 01:52:47');
MariaDB DAYNAME Function On DateTime Expression
MariaDB DAYNAME Function On DateTime Expression

Example 3 – MariaDB DAYNAME() Function on CURDATE Function.

SELECT DAYNAME(CURDATE());
MariaDB DAYNAME Function On CURDATE
MariaDB DAYNAME Function On CURDATE

This is how we can implement MariaDB DAYNAME Function.

Read How to Remove User in MariaDB

MariaDB DAYOFWEEK Function

Here, we are going to learn about the MariaDB DAYOFWEEK Function, and its syntax with examples.

MariaDB DAYOFWEEK() Function returns the weekday index corresponding to a particular Date.

The syntax for the DAYOFWEEK() Function in MariaDB is given below.

DAYOFWEEK( date_value )

Here, date_value is the Date or DateTime expression from which the weekday index will be extracted.

Several examples for the DAYOFWEEK() Function in MariaDB are given below.

Example 1 – MariaDB DAYOFWEEK() Function in Date expression.

SELECT DAYOFWEEK('2004-06-21');

In the example, we have passed a date value to the DAYOFWEEK function and it will return the data of the week as an integer value.

MariaDB DAYOFWEEK Function
MariaDB DAYOFWEEK Function

In the output, we got 2 as a result. It means it will be the 2nd day of the week.

Example 2 – MariaDB DAYOFWEEK() Function in DateTime expression.

SELECT DAYOFWEEK('2004-06-11 01:52:47');
MariaDB DAYOFWEEK Function On DateTime Expression
MariaDB DAYOFWEEK Function On DateTime Expression

Example 3 – MariaDB DAYOFWEEK() Function on CURDATE Function.

SELECT DAYOFWEEK(CURDATE());
MariaDB DAYOFWEEK Function On CURDATE Function
MariaDB DAYOFWEEK Function On CURDATE Function

This is how we can implement MariaDB DAYOFWEEK Function.

Read How to Grant All Privileges in MariaDB

MariaDB LAST_DAY Function

Here, we are going to learn about the MariaDB LAST_DAY Function, and its syntax with examples.

MariaDB LAST_DAY() Function returns the last day of the month corresponding to a particular date.

The syntax for the LAST_DAY() Function in MariaDB is given below.

LAST_DAY( date_value )

Here, date_value is the Date or DateTime expression from which the last day of the month will be extracted.

Several examples for the LAST_DAY() Function in MariaDB are given below.

Example 1 – MariaDB LAST_DAY() Function in Date expression.

SELECT LAST_DAY('2004-06-21');

In the example, we have passed 2004-06-21 as a date value to the LAST_DAY function. And it will return the last day of that particular month.

MariaDB LAST_DAY Function
MariaDB LAST_DAY Function

Example 2 – MariaDB LAST_DAY() Function in DateTime expression.

SELECT LAST_DAY('2004-06-11 01:52:47');
MariaDB LAST_DAY Function On DateTime Expression
MariaDB LAST_DAY Function On DateTime Expression

Example 3 – MariaDB LAST_DAY() Function on CURDATE Function.

SELECT LAST_DAY(CURDATE());
MariaDB LAST_DAY Function On CURDATE Function
MariaDB LAST_DAY Function On CURDATE Function

This is how we can implement MariaDB LAST_DAY Function.

Read How to Create View in MariaDB

MariaDB NOW Function

Here, we are going to learn about the MariaDB NOW Function, and its syntax with examples.

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

The syntax for the NOW() Function in MariaDB is given below.

NOW()

Here, the NOW() Function does not have any parameters or arguments in the syntax in MariaDB.

MariaDB NOW() function returns the date and time in ‘YYYY-MM-DD HH:MM:SS‘ format if used in a string context and YYYYMMDDHHMMSS format if used in a numeric context.

NOTE – CURRENT_TIMESTAMP, LOCALTIME AND LOCALTIMESTAMP Functions are synonyms of NOW() Function.

Several examples for the NOW() Function in MariaDB are given below.

Example 1 – MariaDB NOW() Function in String Context.

SELECT NOW();
MariaDB NOW Function
MariaDB NOW Function

Example 2 – MariaDB NOW() Function in Numeric Context.

SELECT NOW() + 0;
MariaDB NOW Function In Numeric Context
MariaDB NOW Function In Numeric Context

Example 3 – MariaDB NOW() Function plus one.

SELECT NOW() + 1;
Adding to MariaDB NOW Function
Adding to MariaDB NOW Function

This is how we can implement MariaDB NOW Function.

Read Replace Function in MariaDB

MariaDB MONTH Function

Here, we are going to learn about the MariaDB MONTH Function, and its syntax with examples.

MariaDB MONTH() Function returns the month corresponding to a particular date.

The syntax for the MONTH() Function in MariaDB is given below.

MONTH( date_value )

Here, date_value is the Date or DateTime expression from which the month will be extracted.

Several examples for the MONTH() Function in MariaDB are given below.

Example 1 – MariaDB MONTH() Function in Date expression.

SELECT MONTH('2004-06-21');
MariaDB MONTH Function
MariaDB MONTH Function

Example 2 – MariaDB MONTH() Function in DateTime expression.

SELECT MONTH('2004-06-11 01:52:47');
MariaDB MONTH Function In DateTime Expression
MariaDB MONTH Function In DateTime Expression

Example 3 – MariaDB MONTH() Function on CURDATE Function.

SELECT MONTH(CURDATE());
MariaDB MONTH Function On CURDATE Function
MariaDB MONTH Function On CURDATE Function

This is how we can implement MariaDB MONTH Function.

Read How to import CSV files in MariaDB

MariaDB Year Function

Here, we are going to learn about the MariaDB YEAR Function, and its syntax with examples.

MariaDB YEAR() Function returns the year corresponding to a particular date.

The syntax for the YEAR() Function in MariaDB is given below.

YEAR( date_value )

Here, date_value is the Date or DateTime expression from which the year will be extracted.

Several examples for the YEAR() Function in MariaDB are given below.

Example 1 – MariaDB YEAR() Function in Date expression.

SELECT YEAR('2004-06-21');
MariaDB YEAR Function
MariaDB YEAR Function

Example 2 – MariaDB YEAR() Function in DateTime expression.

SELECT YEAR('2004-06-11 01:52:47');
MariaDB YEAR Function On DateTime Expression
MariaDB YEAR Function On DateTime Expression

Example 3 – MariaDB YEAR() Function on CURDATE Function.

SELECT YEAR(CURDATE());
MariaDB YEAR Function On CURDATE Function
MariaDB YEAR Function On CURDATE Function

This is how we can implement MariaDB YEAR Function.

Read MariaDB Vs SQLite

MariaDB TIMEDIFF Function

Here, we will learn about MariaDB TIMEDIFF Function with its syntax and example.

The MariaDB TIMEDIFF Function simply returns the difference in time between two different Time/DateTime expressions. It simply takes two Time/DateTime expressions as parameters and calculates the difference between them.

The syntax for the TIMEDIFF Function in MariaDB is given below.

TIMEDIFF( time1, time2 )

Here, time1 and time2 is the parameter whose values are calculated using the TIMEDIFF function in MariaDB.

The calculation is done like this: time1 – time2

Several examples of the TIMEDIFF function in MariaDB.

SELECT TIMEDIFF('2004-01-26 12:15:48', '2004-01-26 02:17:57');
MariaDB TIMEDIFF Function
MariaDB TIMEDIFF Function
SELECT TIMEDIFF('10:45:34', '12:17:48');
Negative Value In MariaDB TIMEDIFF Function
Negative Value In MariaDB TIMEDIFF Function
SELECT TIMEDIFF('2004-01-26 10:40:28', '11:30:48');
TIMEDIFF Function In MariaDB
TIMEDIFF Function In MariaDB

We can also calculate the days between a particular DateTime expression and the current time using the NOW() function which returns the current date and time.

To find the time difference between any particular DateTime (be it ‘2022-01-16 10:40:28’ ), type the following command.

SELECT TIMEDIFF(NOW(), '2022-01-16 10:40:28');
MariaDB TIMEDIFF Function Using NOW Function
MariaDB TIMEDIFF Function Using NOW Function

In the above example, we are calculating the Time difference between the current DateTime value and ‘2022-01-16 10:40:28’. Note that the DateTime format is not of the United States of America. It is in ‘YYY-MM-DD HH:MM:SS’ format.

This is how the MariaDB TIMEDIFF function works.

Read MariaDB Timestamp

MariaDB STR_TO_DATE Function

Here, we are going to learn about the MariaDB STR_TO_DATE Function, and its syntax with examples.

MariaDB STR_TO_DATE() Function takes a string returns and returns a date according to format mask.

The syntax for the STR_TO_DATE() Function in MariaDB is given below.

STR_TO_DATE(string, format_mask)

Here, the parameters and arguments are given below.

  • string – it is the string value to be formatted as a date.
  • format_mask – the format that we want to apply on a date and below we have a list of options for format_mask which can also be used in combination.
ValueDescription
%aWeekday name abbreviated from Sun to Sat
%bMonth name abbreviated from Jan to Dec
%cMonth as a numeric value ranging from 0 to 12
%DDay of the month as a numeric value, followed by a suffix like 1st, 2nd, 3rd, and so on
%dDay of the month as a numeric value from 01 to 31
%eDay of the month as a numeric value from 0 to 31
%fMicroseconds ranging from 000000 to 999999
%HHour from 00 to 23
%hHour from 00 to 12
%IHour from 00 to 12
%iMinutes from 00 to 59
%jDay of the year ranging from 001 to 366
%kHour (00 to 23)
%lHour (1 to 12)
%MMonth name in full (January to December)
%mMonth name as a numeric value from 00 to 12
%pAM or PM
%rTime in 12 hour AM or PM format (hh:mm:ss AM/PM)
%SSeconds from 00 to 59
%TTime in 24 hour format (hh:mm:ss)
%UThe week where Sunday is the first day of the week (00 to 53)
%uThe week where Monday is the first day of the week (00 to 53)
%VThe week where Sunday is the first day of the week (01 to 53)
Used with %X
%vThe week where Monday is the first day of the week (01 to 53)
Used with %X
%WWeekday name in full (Sunday to Saturday)
%wDay of the week where Sunday=0 and Saturday=6
%XYear for the week where Sunday is the first day of the week
Used with %V
%xYear for the week where Monday is the first day of the week
Used with %v
%YYear as a numeric, 4-digit value
%yYear as a numeric, 2-digit value

Several examples for the STR_TO_DATE() Function in MariaDB are given below.

Example 1 – MariaDB STR_TO_DATE() Function in Date expression.

SELECT STR_TO_DATE('July 12 2004', '%M %d %Y');
MariaDB STR_TO_DATE Function
MariaDB STR_TO_DATE Function
SELECT STR_TO_DATE('Thursday, July 12, 2004', '%W, %M %e, %Y');
MariaDB STR_TO_DATE Function Using Combination
MariaDB STR_TO_DATE Function Using Combination
SELECT STR_TO_DATE('2004,8,20 08', '%Y,%m,%d %h');
STR_TO_DATE Function In MariaDB
STR_TO_DATE Function In MariaDB
SELECT STR_TO_DATE('11,43,21', '%h,%i,%s');
MariaDB STR_TO_DATE Function Example
MariaDB STR_TO_DATE Function

This is how we can implement MariaDB STR_TO_DATE Function.

Related MariaDB tutorials:

In this tutorial, we have learned about MariaDB Date Functions. Additionally, we have covered the following topics.

  • MariaDB Date Functions
  • MariaDB Add Date Function
  • MariaDB Current Date Function
  • MariaDB CURDATE Function
  • MariaDB DATEDIFF Function
  • MariaDB TIMESTAMPDIFF Function
  • MariaDB DAY Function
  • MariaDB DATE_ADD Function
  • MariaDB DATE_SUB Function
  • MariaDB DATE_FORMAT Function
  • MariaDB DAYNAME Function
  • MariaDB DAYOFWEEK Function
  • MariaDB LAST_DAY Function
  • MariaDB NOW Function
  • MariaDB MONTH Function
  • MariaDB YEAR Function
  • MariaDB TIMEDIFF Function
  • MariaDB STR_TO_DATE Function