MySQL DAYNAME Function

 MySQL DAYNAME Function

Summary: in this tutorial, you will learn how to use the MySQL DAYNAME function to get the name of a weekday for a given date.

Introduction to MySQL DAYNAME function

MySQL DAYNAME the function returns the name of a weekday for a specified date. The following illustrates the syntax of the DAYNAME function:

DAYNAME(date);

The DAYNAME the function accepts 1 argument which is a date that you want to get the name of its weekday.

If the date is NULL or invalid e.g., 2017-02-30, the DAYNAME function returns NULL.

MySQL DAYNAME function examples

The following example returns the name of a weekday for January 1st, 2000.

mysql> SELECT DAYNAME('2000-01-01') dayname;
+----------+
| dayname  |
+----------+
| Saturday |
+----------+
1 row in set (0.00 sec)

By default, MySQL returns the name of a weekday in the language controlled by the lc_time_names system variable.

mysql> SELECT @@lc_time_names;
+-----------------+
| @@lc_time_names |
+-----------------+
| en_US           |
+-----------------+
1 row in set (0.00 sec)

As you can see, currently, the locale is set to en_US.

To get the day name of a weekday in a specific locale, you need to change the value of the lc_time_names variable. For example, the following statement sets the locale to French:

mysql> SET @@lc_time_names = 'fr_FR';
Query OK, 0 rows affected (0.00 sec)

Now, let’s query the name of the weekday for January 1st, 2000:

mysql> SELECT DAYNAME('2000-01-01') dayname;
+---------+
| dayname |
+---------+
| samedi  |
+---------+
1 row in set (0.00 sec)

As you can see, the weekday name has been changed to French.

The following table shows the valid locales for lc_time_names supported by MySQL:

Albanian – Albania	sq_AL
Arabic – Algeria	ar_DZ
Arabic – Bahrain	ar_BH
Arabic – Egypt	ar_EG
Arabic – India	ar_IN
Arabic – Iraq	ar_IQ
Arabic – Jordan	ar_JO
Arabic – Kuwait	ar_KW
Arabic – Lebanon	ar_LB
Arabic – Libya	ar_LY
Arabic – Morocco	ar_MA
Arabic – Oman	ar_OM
Arabic – Qatar	ar_QA
Arabic – Saudi Arabia	ar_SA
Arabic – Sudan	ar_SD
Arabic – Syria	ar_SY
Arabic – Tunisia	ar_TN
Arabic – United Arab Emirates	ar_AE
Arabic – Yemen	ar_YE
Basque – Basque	eu_ES
Belarusian – Belarus	be_BY
Bulgarian – Bulgaria	bg_BG
Catalan – Spain	ca_ES
Chinese – China	zh_CN
Chinese – Hong Kong	zh_HK
Chinese – Taiwan Province of China	zh_TW
Croatian – Croatia	hr_HR
Czech – Czech Republic	cs_CZ
Danish – Denmark	da_DK
Dutch – Belgium	nl_BE
Dutch – The Netherlands	nl_NL
English – Australia	en_AU
English – Canada	en_CA
English – India	en_IN
English – New Zealand	en_NZ
English – Philippines	en_PH
English – South Africa	en_ZA
English – United Kingdom	en_GB
English – United States	en_US
English – Zimbabwe	en_ZW
Estonian – Estonia	et_EE
Faroese – Faroe Islands	fo_FO
Finnish – Finland	fi_FI
French – Belgium	fr_BE
French – Canada	fr_CA
French – France	fr_FR
French – Luxembourg	fr_LU
French – Switzerland	fr_CH
Galician – Spain	gl_ES
German – Austria	de_AT
German – Belgium	de_BE
German – Germany	de_DE
German – Luxembourg	de_LU
German – Switzerland	de_CH
Greek – Greece	el_GR
Gujarati – India	gu_IN
Hebrew – Israel	he_IL
Hindi – India	hi_IN
Hungarian – Hungary	hu_HU
Icelandic – Iceland	is_IS
Indonesian – Indonesia	id_ID
Italian – Italy	it_IT
Italian – Switzerland	it_CH
Japanese – Japan	ja_JP
Korean – Republic of Korea	ko_KR
Latvian – Latvia	lv_LV
Lithuanian – Lithuania	lt_LT
Macedonian – FYROM	mk_MK
Malay – Malaysia	ms_MY
Mongolia – Mongolian	mn_MN
Norwegian – Norway	no_NO
Norwegian(Bokmål) – Norway	nb_NO
Polish – Poland	pl_PL
Portugese – Brazil	pt_BR
Portugese – Portugal	pt_PT
Romanian – Romania	ro_RO
Russian – Russia	ru_RU
Russian – Ukraine	ru_UA
Serbian – Yugoslavia	sr_RS
Slovak – Slovakia	sk_SK
Slovenian – Slovenia	sl_SI
Spanish – Argentina	es_AR
Spanish – Bolivia	es_BO
Spanish – Chile	es_CL
Spanish – Columbia	es_CO
Spanish – Costa Rica	es_CR
Spanish – Dominican Republic	es_DO
Spanish – Ecuador	es_EC
Spanish – El Salvador	es_SV
Spanish – Guatemala	es_GT
Spanish – Honduras	es_HN
Spanish – Mexico	es_MX
Spanish – Nicaragua	es_NI
Spanish – Panama	es_PA
Spanish – Paraguay	es_PY
Spanish – Peru	es_PE
Spanish – Puerto Rico	es_PR
Spanish – Spain	es_ES
Spanish – United States	es_US
Spanish – Uruguay	es_UY
Spanish – Venezuela	es_VE
Swedish – Finland	sv_FI
Swedish – Sweden	sv_SE
Tamil – India	ta_IN
Telugu – India	te_IN
Thai – Thailand	th_TH
Turkish – Turkey	tr_TR
Ukrainian – Ukraine	uk_UA
Urdu – Pakistan	ur_PK
Vietnamese – Viet Nam	vi_VN

See the following orders the table in the sample database:

The following statement returns the order count grouped by name of a weekday in 2004.

SELECT 
    DAYNAME(orderdate) weekday, 
    COUNT(*) total_orders
FROM
    orders
WHERE
    YEAR(orderdate) = 2004
GROUP BY weekday
ORDER BY total_orders DESC;

The number of orders placed on Friday is the highest and there were two orders placed on Sunday.

In this tutorial, you have learned how to use the MySQL DAYNAME function to get the name of weekday for a particular date.