How to Get Day Names in MySQL

Problem:

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

Solution 1:

To extract the day name from a date, 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’.

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:

Solution 2:

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

The result is ‘Sat’.

Discussion:

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:

Solution 3:

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

The result is ‘Samstag’.