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.

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.

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.

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.

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.

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.

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.

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.

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.

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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

SELECT CURRENT_DATE() + 0;

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.

SELECT CURRENT_DATE() + INTERVAL 6 MONTH;

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

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

SELECT CURDATE() + 0;

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

SELECT CURDATE() + 1;

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

SELECT CURDATE() + INTERVAL 6 MONTH;

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

SELECT DATEDIFF('2011-07-16 11:34:44', '2013-03-24');

SELECT DATEDIFF('2004-06-29', '2008-11-21');

SELECT DATEDIFF('2008-11-21', '2004-06-29');

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.

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.

Example 2 – Negative value in TIMESTAMPDIFF in MariaDB.

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

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;

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;

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;

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;

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;

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;

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

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

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

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

SELECT DAY(CURDATE());

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

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

SELECT DATE_ADD('2004-06-19 09:46:27', INTERVAL -39 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);

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

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

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

SELECT DATE_ADD('2004-06-19', INTERVAL 19 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);

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

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

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

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

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

SELECT DATE_ADD('2004-06-19', INTERVAL 17 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);

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

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

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

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

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

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

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

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

SELECT DATE_ADD('2004-06-19', INTERVAL '17-8' 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);

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.

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

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

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

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

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

SELECT DAYNAME('2004-06-11 01:52:47');

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

SELECT DAYNAME(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.

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

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

SELECT DAYOFWEEK(CURDATE());

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.

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

SELECT LAST_DAY('2004-06-11 01:52:47');

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

SELECT LAST_DAY(CURDATE());

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

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

SELECT NOW() + 0;

Example 3 – MariaDB NOW() Function plus one.

SELECT NOW() + 1;

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

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

SELECT MONTH('2004-06-11 01:52:47');

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

SELECT MONTH(CURDATE());

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

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

SELECT YEAR('2004-06-11 01:52:47');

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

SELECT YEAR(CURDATE());

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

SELECT TIMEDIFF('10:45:34', '12:17:48');

SELECT TIMEDIFF('2004-01-26 10:40:28', '11:30:48');

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

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.

Value	Description
%a	Weekday name abbreviated from Sun to Sat
%b	Month name abbreviated from Jan to Dec
%c	Month as a numeric value ranging from 0 to 12
%D	Day of the month as a numeric value, followed by a suffix like 1st, 2nd, 3rd, and so on
%d	Day of the month as a numeric value from 01 to 31
%e	Day of the month as a numeric value from 0 to 31
%f	Microseconds ranging from 000000 to 999999
%H	Hour from 00 to 23
%h	Hour from 00 to 12
%I	Hour from 00 to 12
%i	Minutes from 00 to 59
%j	Day of the year ranging from 001 to 366
%k	Hour (00 to 23)
%l	Hour (1 to 12)
%M	Month name in full (January to December)
%m	Month name as a numeric value from 00 to 12
%p	AM or PM
%r	Time in 12 hour AM or PM format (hh:mm:ss AM/PM)
%S	Seconds from 00 to 59
%T	Time in 24 hour format (hh:mm:ss)
%U	The week where Sunday is the first day of the week (00 to 53)
%u	The week where Monday is the first day of the week (00 to 53)
%V	The week where Sunday is the first day of the week (01 to 53) Used with %X
%v	The week where Monday is the first day of the week (01 to 53) Used with %X
%W	Weekday name in full (Sunday to Saturday)
%w	Day of the week where Sunday=0 and Saturday=6
%X	Year for the week where Sunday is the first day of the week Used with %V
%x	Year for the week where Monday is the first day of the week Used with %v
%Y	Year as a numeric, 4-digit value
%y	Year 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');

SELECT STR_TO_DATE('Thursday, July 12, 2004', '%W, %M %e, %Y');

SELECT STR_TO_DATE('2004,8,20 08', '%Y,%m,%d %h');

SELECT STR_TO_DATE('11,43,21', '%h,%i,%s');

This is how we can implement MariaDB STR_TO_DATE Function.

Related MariaDB tutorials:

• How to Drop Column from MariaDB Table
• MariaDB ERROR 1064
• How to Create Function in MariaDB
• How to Create Trigger in MariaDB
• MariaDB Vs SQL Server

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