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
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading