I need to find the closest date that is a Saturday less than or equal to a date given by a user. For example, 1/1/07 would return 12/30/06, 2/26/07 would return 2/24/07, etc. How would you go about this?
My approach would be to take the day of the week of the given date, use this to come up with a number between 0 and 6, and subtract this number of days from the given date. This is basically all there is to it, except for minor implementation details.
The details consist in whether Saturday is the seventh day of your week or the sixth, and then finding the right date function(s) in your particular database system to yield the day of week. Most likely there will be some DATEPART or EXTRACT function.
For example, MySQL has two: WEEKDAY(date) returns the weekday index 0=Monday, 1=Tuesday, ... 6=Sunday), while DAYOFWEEK(date) returns the weekday index 1=Sunday, 2=Monday, ..., 7=Saturday. Using the latter function, as well as the MOD (modulo) function (to change 7 to 0, because you said "closest saturday less than or equal to a date given")...
select inputdate , date_sub(inputdate , interval mod(dayofweek(inputdate),7) day ) as saturday from ( select '2007-01-01' as inputdate union all select '2007-02-23' union all select '2007-02-24' union all select '2007-02-25' union all select '2007-02-26' union all select '2007-02-27' union all select '2007-02-28' union all select '2007-03-01' union all select '2007-03-02' union all select '2007-03-03' union all select '2007-03-04' ) as d inputdate saturday 2007-01-01 2006-12-30 2007-02-23 2007-02-17 2007-02-24 2007-02-24 2007-02-25 2007-02-24 2007-02-26 2007-02-24 2007-02-27 2007-02-24 2007-02-28 2007-02-24 2007-03-01 2007-02-24 2007-03-02 2007-02-24 2007-03-03 2007-03-03 2007-03-04 2007-03-03
This was first published in February 2007