Get Weekday name from index number in MYSQL

Question

I have a table which stores 0-6 as weekday value, and I want to display weekday name. E.g. if value is 0, it will display Sunday, if value is 1, it will display Monday, likewise. Is there any inbuilt MySQL function for getting day name from Index?

Thanks in Advance!!

Answer 1

As @Aliminator mentioned, you could use DAYNAME with a DATE.

However, if you don't want to change your schema, here is a nifty hack for you:

 SELECT DAYNAME(CONCAT("1970-09-2", dayIndex)) FROM your_table;

This is based on the fact that 1970-09-20 was a Sunday, the 21st was a Monday, and so on.

Answer 2

DAYNAME( date_value ) is available in MySql; however, this takes a date, not an int. So if you have the date available, you can just use DAYNAME(CURDATE()), DAYNAME('2014-01-07'), etc.

If all you have available is the int representing the weekday, then a CASE statement might be your best bet.

CASE WHEN 0 THEN 'Sunday' WHEN 1 THEN 'Monday' ..... ELSE '' END