Q

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.

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

Neat, eh?

This was first published in February 2007

Dig deeper on Oracle and SQL

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close