Is it possible to use the current dayname in the where clause in MySQL?

Question

I am trying to create a view in MySQL based on the current day of the week. I am creating a table to keep track of tasks based on the day of the week. For example, some tasks will happen every Tuesday, some will happen on Wednesday and Friday, etc.

I decided to set the table up with a column for each day of the week. If the task needs to be executed on that day I will store a 1 in the column, otherwise it will be a 0. The table looks like this:

| ID | Monday | Tuesday | Wednesday | Thursday | Friday |    Task     |
-----------------------------------
| 1  |   0    |    1    |     0     |     0    |   0    | "SomeTask"  |
| 2  |   0    |    0    |     1     |     0    |   1    | "SomeTask"  |
| 3  |   0    |    1    |     0     |     0    |   0    | "SomeTask"  |

I would like to create a SELECT statement that will be used in a view to show the tasks that need to be executed on the current day. In other words, today is Tuesday so I would like to a query that will get the rows with the ID of 1 and 3 to show up.

I tried the following , but it didn't work:

SELECT * FROM MyTasks WHERE DAYNAME(curdate()) = 1

Is there a better way to format the table? Is there anyway to use DAYNAME in the WHERE clause? Any suggestions?

Answer 1

You can use case like this:

SELECT * FROM `MyTasks` WHERE (CASE DAYNAME(NOW())
            WHEN 'Monday'    THEN `Monday`=1
            WHEN 'Tuesday'   THEN `Tuesday`=1
            WHEN 'Wednesday' THEN `Wednesday`=1
            WHEN 'Thursday'  THEN `Thursday`=1
            WHEN 'Friday'    THEN `Friday`=1
            END)

Apart from that I don't see any way of you accomplishing this, as the column names are static and can't be dynamically built up based on other functions etc

Answer 2

you can get day name of using DAYNAME(curdate()) function this is returning Thursday (today is 2015-03-05) but,

According to your table structure have to use 1 of following queries

01 SELECT * FROM MyTasks WHERE (

CASE DAYNAME(curdate())

        WHEN 'Monday'    THEN `Monday`=1
        WHEN 'Tuesday'   THEN `Tuesday`=1
        WHEN 'Wednesday' THEN `Wednesday`=1
        WHEN 'Thursday'  THEN `Thursday`=1
        WHEN 'Friday'    THEN `Friday`=1
        END)

02 SELECT * FROM MyTasks WHERE (

CASE weekday(curdate())

        WHEN 0    THEN `Monday`=1
        WHEN 1    THEN `Tuesday`=1
        WHEN 2    THEN `Wednesday`=1
        WHEN 3    THEN `Thursday`=1
        WHEN 4    THEN `Friday`=1
        END)