How DAYNAME() Works in MariaDB

In MariaDB, DAYNAME() is a built-in date and time function that returns the name of the weekday for a given date.

It accepts one argument, which is the date you want to extract the day name from.

Syntax

The syntax goes like this:

DAYNAME(date)

Where date is the date to get the day name from.

Example

Here’s an example:

SELECT DAYNAME('2045-10-17');

Result:

+-----------------------+
| DAYNAME('2045-10-17') |
+-----------------------+
| Tuesday               |
+-----------------------+

Language

The language used for the day name is controlled by the value of the lc_time_names system variable.

Here’s an example that shows how this affects the result:

SET lc_time_names = 'de_DE';
SELECT DAYNAME('2045-10-17');

Result:

+-----------------------+
| DAYNAME('2045-10-17') |
+-----------------------+
| Dienstag              |
+-----------------------+

And switching back to en_US, which is the default:

SET lc_time_names = 'en_US';
SELECT DAYNAME('2045-10-17');

Result:

+-----------------------+
| DAYNAME('2045-10-17') |
+-----------------------+
| Tuesday               |
+-----------------------+

Here’s a list of locales supported by MariaDB, and here’s how to return your own list of available locales.

While we’re at it, here’s a post explaining how to check the value of your lc_time_names system variable.

Datetime Values

It also works with datetime values:

SELECT DAYNAME('2030-01-25 10:30:45');

Result:

+--------------------------------+
| DAYNAME('2030-01-25 10:30:45') |
+--------------------------------+
| Friday                         |
+--------------------------------+

Zero Days

Having a zero day in the date returns null:

SELECT DAYNAME('2030-00-00');

Result:

+-----------------------+
| DAYNAME('2030-00-00') |
+-----------------------+
| NULL                  |
+-----------------------+

Numeric Dates

It’s also possible to pass dates as a number, as long as it makes sense as a date.

Example

SELECT DAYNAME(20201108);

Result:

+-------------------+
| DAYNAME(20201108) |
+-------------------+
| Sunday            |
+-------------------+

Two-digit years are fine:

SELECT DAYNAME(201108);

Result:

+-----------------+
| DAYNAME(201108) |
+-----------------+
| Sunday          |
+-----------------+

But it must make sense as a date. Here’s what happens if I increase the month part to an invalid month:

SELECT DAYNAME(209908);

Result:

+-----------------+
| DAYNAME(209908) |
+-----------------+
| NULL            |
+-----------------+

Other Delimiters

You can use other delimiters for the date. MariaDB is quite forgiving when it comes to delimiters on dates. Here are some valid examples:

SELECT 
    DAYNAME('2027/08/19'),
    DAYNAME('2027,08,19'),
    DAYNAME('2027:08:19'),
    DAYNAME('2027;08!19');

Result (using vertical output):

DAYNAME('2027/08/19'): Thursday
DAYNAME('2027,08,19'): Thursday
DAYNAME('2027:08:19'): Thursday
DAYNAME('2027;08!19'): Thursday

Current Date

We can pass NOW() as the date argument to use the current date:

SELECT 
    NOW(),
    DAYNAME(NOW());

Result:

+---------------------+----------------+
| NOW()               | DAYNAME(NOW()) |
+---------------------+----------------+
| 2021-05-13 13:29:10 | Thursday       |
+---------------------+----------------+

Invalid Arguments

When passed an invalid argument, DAYNAME() returns null:

SELECT DAYNAME('Dang!');

Result:

+------------------+
| DAYNAME('Dang!') |
+------------------+
| NULL             |
+------------------+

Missing Argument

Calling DAYNAME() with the wrong number of arguments, or without passing any arguments, results in an error:

SELECT DAYNAME();

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYNAME'

And another example:

SELECT DAYNAME('2030-01-25', '2045-05-08');

Result:

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DAYNAME'