Ask the Expert

Closest Saturday less than or equal to a date given

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?

    Requires Free Membership to View

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

Neat, eh?

This was first published in February 2007

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: