MySQL Cookbook, 2nd Edition

Finding Dates for Any Weekday of a Given Week

Problem

You want to compute the date of some weekday for the week in which a given date lies. For example, suppose that you want to know the date of the Tuesday that falls in the same week as 2006-07-09.

Solution

This is an application of date shifting. Figure out the number of days between the starting weekday of the given date and the desired day, and shift the date by that many days.

Discussion

This section and the next describe how to convert one date to another when the target date is specified in terms of days of the week. To solve such problems, you need to know day-of-week values. Suppose you begin with a target date of 2006-07-09. If you want to know what date it is on Tuesday of the week in which that date lies, the calculation depends on what weekday it is. If it’s a Monday, you add a day to produce 2006-07-10, but if it’s a Wednesday, you subtract a day to produce 2006-07-08.

MySQL provides two functions that are useful here. DAYOFWEEK() treats Sunday as the first day of the week and returns 1 through 7 for Sunday through Saturday. WEEKDAY() treats Monday as the first day of the week and returns 0 through 6 for Monday through Sunday. (The examples shown here use DAYOFWEEK().) Another kind of day-of-week operation involves determining the name of the day. DAYNAME() can be used for that.

Calculations that determine one day of the week from another depend on the day you start from as well as the day you want to reach. I find it easiest ...