Below are three approaches you can use to get the day name from a date in MariaDB.

Two of these approaches return the full day name, and one returns the short day name.

The DAYNAME() Function

The DAYNAME() function is designed specifically for returning the day name from a date. Simply pass the date when calling the function, and it will return the full day name.

Example:

SELECT DAYNAME('2021-08-19');

Result:

+-----------------------+
| DAYNAME('2021-08-19') |
+-----------------------+
| Thursday              |
+-----------------------+

The language used for the day name is controlled by the value of the lc_time_names system variable. See DAYNAME() for an example of how this affects the output.

The DATE_FORMAT() Function

The DATE_FORMAT() function formats a given date into a specified format. You specify the date and the format when you call the function.

You can return the full weekday name by passing %W as the format string.

Example:

SELECT DATE_FORMAT('2021-08-19', '%W');

Result:

+---------------------------------+
| DATE_FORMAT('2021-08-19', '%W') |
+---------------------------------+
| Thursday                        |
+---------------------------------+

As with DAYNAME(), the language used for the day name is controlled by the value of the lc_time_names system variable. However, the DATE_FORMAT() function accepts an optional third argument that allows you to specify the locale.

Here’s an example of specifying a locale:

SELECT DATE_FORMAT('2021-08-19', '%W', 'ca_ES');

Result:

+------------------------------------------+
| DATE_FORMAT('2021-08-19', '%W', 'ca_ES') |
+------------------------------------------+
| dijous                                   |
+------------------------------------------+

Short Day Name

Passing %a to the DATE_FORMAT() function returns the short weekday name.

Example:

SELECT DATE_FORMAT('2021-08-19', '%a');

Result:

+---------------------------------+
| DATE_FORMAT('2021-08-19', '%a') |
+---------------------------------+
| Thu                             |
+---------------------------------+

This could be replicated with the DAYNAME() function by using LEFT() to grab the first 3 characters from the weekday name.

Example:

SELECT LEFT(DAYNAME('2021-08-19'), 3);

Result:

+--------------------------------+
| LEFT(DAYNAME('2021-08-19'), 3) |
+--------------------------------+
| Thu                            |
+--------------------------------+

The same effect could also be achieved by using a function like CAST() to convert the result to a data type with three characters.

However, although this approach works in en_US, it might not always work in other languages. For example:

SET lc_time_names = 'th_TH';
SELECT 
    DATE_FORMAT('2021-08-19', '%a') AS "Short 1",
    LEFT(DAYNAME('2021-08-19'), 3) AS "Short 2",
    DAYNAME('2021-08-19') AS "Full";

Result:

+---------+-----------+--------------------------+
| Short 1 | Short 2   | Full                     |
+---------+-----------+--------------------------+
| พฤ.     | พฤห       | พฤหัสบดี                   |
+---------+-----------+--------------------------+