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 function returns the name of a weekday for a specified date. The following illustrates the syntax of the DAYNAME function:
DAYNAME(date);
Code language: SQL (Structured Query Language) (sql)
The DAYNAME 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)
Code language: SQL (Structured Query Language) (sql)
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)
Code language: SQL (Structured Query Language) (sql)
As you can see, currently, the locale is set to en_US.
To get day name 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)
Code language: SQL (Structured Query Language) (sql)
Now, let’s query the name of weekday for January 1st, 2000:
mysql> SELECT DAYNAME('2000-01-01') dayname;
+---------+
| dayname |
+---------+
| samedi |
+---------+
1 row in set (0.00 sec)
Code language: SQL (Structured Query Language) (sql)
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 table in the sample database:
The following statement returns the order count grouped by name of weekday in 2004.
SELECT
DAYNAME(orderdate) weekday,
COUNT(*) total_orders
FROM
orders
WHERE
YEAR(orderdate) = 2004
GROUP BY weekday
ORDER BY total_orders DESC;Code language: SQL (Structured Query Language) (sql)
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.