Design option for 'recurring tasks'

I would create a task table to insert my tasks into.

taskTable
|taskID  |Freq   |runAt       |
-------------------------------
|1       |daily  |1           |
|2       |daily  |0           |
|3       |weekly |5           |
|4       |weekly |1           |
|5       |monthly|15          |
|6       |monthly|1           |
|7       |once   |2013-7-4    |

runAt for dailies is not ever considered so it doesn't matter what value is entered.

runAt for weekly items is the day of the week that the task is to run.

runAt for mothly is the day of the month that the task is to run (month end tasks I usually run on the first since is saves the hassle of dealing with which day the month ends on although you could use this to figure that out

lastDayOfMonth = datePart(d,dateadd(s,-1,dateadd(mm, datediff(m,0,getdate())+1,0)))

runAt for once is the actual day the task is to run.

Then I'd create a task to run daily to see what needed to be run.

select  taskID
from    taskTable
where   (freq = 'once' and runAt = convert(varchar(10),getDate(),21))
  or    freq = 'daily'
  or    (freq = 'weekly' and runAt = datePart(dw,getDate()))
  or    (freq = 'monthly' and runAt = datePart(d,getDate())

This query gives me all the taskID for any tasks that I need to run.

Not sure if this is what you were looking for but hopefully you'll find something useful in it.

I will try to use something tried and tested solution. Unix Cron Table solution. This solution is also used by a lot of other tools like Hudson/Jenkins.

From Wikipedia.

*    *    *    *    *  command to be executed
┬    ┬    ┬    ┬    ┬
│    │    │    │    │
│    │    │    │    │
│    │    │    │    └───── day of week (0 - 7) (0 or 7 are Sunday, or use names)
│    │    │    └────────── month (1 - 12)
│    │    └─────────────── day of month (1 - 31)
│    └──────────────────── hour (0 - 23)
└───────────────────────── min (0 - 59)

Also, it allows shortcut names for entries.

Entry      Description                                                             Equivalent To
@yearly    Run once a year at midnight in the morning of January 1              0 0 1 1 *
@annually   
@monthly   Run once a month at midnight in the morning of the first of the month    0 0 1 * *
@weekly    Run once a week at midnight in the morning of Sunday                     0 0 * * 0
@daily     Run once a day at midnight                                               0 0 * * *
@hourly    Run once an hour at the beginning of the hour                        0 * * * *

From here, we get the following table design:

taskID  cronExpression preDefinedDefinition
 1       30 * * * *      null
 2       0 * * * *      @hourly
 3        ...            ....

@Timothy Britt mentioned that this solution does not account for one-time offs. That is true. Linux and/or Unix also has a command named at. It seems that it stores its entries in a separate file and separate daemon monitors this file. If we want to include one-time jobs in this table. We may add extra boolean column OneTimeOnly. Or another table which only includes one time only jobs.