How to Get Day Names in MySQL

Problem:

You want to extract the name of the day of the week from a date in MySQL.

Solution 1: Extracting day name using DAYNAME() function

To extract the day name from a date in MySQL database, use the DAYNAME() function. It only has one argument: the date itself. Let’s see how it works:

SELECT DAYNAME('2022-01-01');

The result is 'Saturday'.

Solution 2: Extracting day name using DATE_FORMAT() function

There is also an alternative to this function: DATE_FORMAT(). DATE_FORMAT() needs two arguments: a date and a format. To get the full day name (e.g. Monday), you need the format '%W;. You can also extract an abbreviated day name (e.g. Mon) by using the format '%a'. Let’s see an example of extracting a short name:

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

The result is 'Sat'.

Solution 3: Extracting day name in another language

You can also extract the day name in another language. To do this, you need to change your locale by setting lc_time_names. Information on this parameter and various locale codes is available in the official MySQL documentation. Note: Changing this parameter overrides your locale for the rest of your code. If you want to return to your old locale, you need to change lc_time_names again. Let’s see how we can use this parameter to extract the day in German:

SET lc_time_names = 'de_DE';
SELECT DAYNAME('2022-01-01');

The result is 'Samstag'.