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 last published in February 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close