CODEWITHSUNDEEP
mysqldatedayofweek
origamifreak2
origamifreak2

Reputation: 193

MySQL DAYOFWEEK() in reverse?

I'm looking for a way in MySQL to convert an integer (0-6) into a day of the week, something like: 

EXAMPLE_FUNCTION(3) = Wednesday.

The DAYOFWEEK(date) function takes a date and gives you a number 0-6 which corresponds to the day of the week (0 = Sunday, 6 = Saturday), so I'm kind of looking for the reverse of this.

Upvotes: 1

Views: 2351

Answers (2)

Pevara
Pevara

Reputation: 14310

The DAYOFWEEK function should be helpful here: https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayname

The only downside is that it expects a date as input, and not a day number. But that should be easily remedied. Just start with an arbitrary date of which you know it falls on a Sunday (like 2016-01-03). Then ADDDATE your day number to it. Now you have a date that falls on the same day as your day number, so you can just run that trough the DAYOFWEEK function.

DAYNAME(ADDDATE("2016-01-03", :day))

See for yourself by running the following samples:

SELECT DAYNAME(ADDDATE("2016-01-03", "3")) # Wednesday
SELECT DAYNAME(ADDDATE("2016-01-03", "0")) # Sunday

Upvotes: 2

mathmorales
mathmorales

Reputation: 83

DROP FUNCTION IF EXISTS DAYNUMBER;
CREATE FUNCTION DAYNUMBER(s INT) RETURNS VARCHAR(10)
RETURN elt(s+1, 'Sunday', 'Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday');

Test:

SELECT DAYNUMBER(1)

outputs 'Monday'.

Upvotes: 5

Related Questions