In MariaDB, you can use the DATE_FORMAT() function to return various date parts from a date. One of the things you can return is the short day name. For example Tue or Wed (instead of Tuesday or Wednesday).

The “Short Day Name” Format Specifier (%a)

The key to returning the short day name when using the DATE_FORMAT() function is to use the applicable format specifier.

In MariaDB, the format specifier for the short day name is: %a

Example

Here’s an example to demonstrate:

SELECT DATE_FORMAT('2023-07-25', '%a');

Result:

+---------------------------------+
| DATE_FORMAT('2023-07-25', '%a') |
+---------------------------------+
| Tue                             |
+---------------------------------+

Here’s another example that runs through the various days in the week:

SELECT 
    DATE_FORMAT('2023-07-24', '%a') AS "1",
    DATE_FORMAT('2023-07-25', '%a') AS "2",
    DATE_FORMAT('2023-07-26', '%a') AS "3",
    DATE_FORMAT('2023-07-27', '%a') AS "4",
    DATE_FORMAT('2023-07-28', '%a') AS "5",
    DATE_FORMAT('2023-07-29', '%a') AS "6",
    DATE_FORMAT('2023-07-30', '%a') AS "7";

Result:

+------+------+------+------+------+------+------+
| 1    | 2    | 3    | 4    | 5    | 6    | 7    |
+------+------+------+------+------+------+------+
| Mon  | Tue  | Wed  | Thu  | Fri  | Sat  | Sun  |
+------+------+------+------+------+------+------+

You can return other date and time units too, but this article is specifically about returning the short day name. See MariaDB Format Strings for a list of format specifiers that can be used with DATE_FORMAT().

Other Approaches

There are other ways to get the short day name from a date that involve chopping off the first part of the day.

However, this can be more prone to error than the above method (plus it’s more convoluted). You could get some unexpected results when using this method, especially when working with different languages.

Here are some examples to demonstrate what I mean.

Instead of using the %a format specifier like we did in the previous example, we could use the %W format specifier to return the full day name, then return shorten that result with a function like LEFT().

Example:

SELECT 
    DATE_FORMAT('2023-07-25', '%W') AS Full,
    LEFT(DATE_FORMAT('2023-07-25', '%W'), 3) AS Short;

Result:

+---------+-------+
| Full    | Short |
+---------+-------+
| Tuesday | Tue   |
+---------+-------+

We could alternatively use a function like CAST() to convert to a data type with only three characters, such as this:

SELECT 
    DATE_FORMAT('2023-07-25', '%W') AS Full,
    CAST(DATE_FORMAT('2023-07-25', '%W') AS CHAR(3)) AS Short;

Result:

+---------+-------+
| Full    | Short |
+---------+-------+
| Tuesday | Tue   |
+---------+-------+

This works fine when working with the English language (and perhaps some other languages). But let’s see what happens when we switch to a different language – say, Japanese:

SELECT 
    DATE_FORMAT('2023-07-25', '%W', 'ja_JP') AS Full,
    LEFT(DATE_FORMAT('2023-07-25', '%W', 'ja_JP'), 3) AS Short;

Result:

+-----------+-----------+
| Full      | Short     |
+-----------+-----------+
| 火曜日     | 火曜日     |
+-----------+-----------+

In this case, there’s no difference.

In contrast, here’s what happens when we use the %a format specifier to return the short day name:

SELECT 
    DATE_FORMAT('2023-07-25', '%W', 'ja_JP') AS Full,
    DATE_FORMAT('2023-07-25', '%a', 'ja_JP') AS Short;

Result:

+-----------+-------+
| Full      | Short |
+-----------+-------+
| 火曜日     | 火    |
+-----------+-------+

So I’d recommend using the %a format specifier whenever possible.