How to get the day name from a date in MariaDB

In MariaDB, you can use the DATE_FORMAT() function to return the day name from a date, either as a short name (such as Tue and Wed) or a long name (such as Tuesday and Wednesday).

Get the short day name

In MariaDB, you can get the short name from a specified date using DATE_FORMAT() function with the %a format specifier, for example:

SELECT DATE_FORMAT('2022-12-12', '%a');

Output:

+---------------------------------+
| DATE_FORMAT('2022-12-12', '%a') |
+---------------------------------+
| Mon                             |
+---------------------------------+

Get the long day name

In MariaDB, you can get the long name from a specified date using the DATE_FORMAT() function with the %W format specifier, for example:

SELECT DATE_FORMAT('2022-12-12', '%W');

Output:

+---------------------------------+
| DATE_FORMAT('2022-12-12', '%W') |
+---------------------------------+
| Monday                          |
+---------------------------------+

Get the day name of current date

The following example shows how to get the day name of current date:

SELECT
  CURRENT_DATE,
  DATE_FORMAT(CURRENT_DATE, '%W') "Full Name",
  DATE_FORMAT(CURRENT_DATE, '%a') "Short Name";

Output:

+--------------+-----------+------------+
| CURRENT_DATE | Full Name | Short Name |
+--------------+-----------+------------+
| 2022-12-12   | Monday    | Mon        |
+--------------+-----------+------------+

Get all weekday names in a week

The following example shows how to get all weekday names, including long and short names:

SELECT
  '2022-12-12' AS "Day",
  DATE_FORMAT('2022-12-12', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-12', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-13' AS "Day",
  DATE_FORMAT('2022-12-13', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-13', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-14' AS "Day",
  DATE_FORMAT('2022-12-14', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-14', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-15' AS "Day",
  DATE_FORMAT('2022-12-15', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-15', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-16' AS "Day",
  DATE_FORMAT('2022-12-16', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-16', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-17' AS "Day",
  DATE_FORMAT('2022-12-17', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-17', '%a') AS "Short Name"
UNION
SELECT
  '2022-12-18' AS "Day",
  DATE_FORMAT('2022-12-18', '%W') AS "Full Name",
  DATE_FORMAT('2022-12-18', '%a') AS "Short Name";

Output:

+------------+-----------+------------+
| Day        | Full Name | Short Name |
+------------+-----------+------------+
| 2022-12-12 | Monday    | Mon        |
| 2022-12-13 | Tuesday   | Tue        |
| 2022-12-14 | Wednesday | Wed        |
| 2022-12-15 | Thursday  | Thu        |
| 2022-12-16 | Friday    | Fri        |
| 2022-12-17 | Saturday  | Sat        |
| 2022-12-18 | Sunday    | Sun        |
+------------+-----------+------------+

Conclusion

This article discusses how to use the DATE_FORMAT() function:

The %a format specifier is used to get short name from a specified date
The %W format specifier is used to get long name from a specified date

You can check out more information about MariaDB date formatting specifiers.