• Home
• Topics
• Oracle development languages
• Oracle and SQL
• Closest Saturday less than or equal to a date given

Closest Saturday less than or equal to a date given

Requires Free Membership to View

Login

• E-mail Address: 

By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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?

Dig Deeper

This was first published in February 2007

More from Related TechTarget Sites

• Data Management
• Business Analytics
• SAP
• SQL Server
• Java
• Data Center
• Content Management
• Financial Applications

• Data Management

  • People, process issues offer path to improve data quality on the cheap

    An emphasis on educating end users and upgrading internal processes can help enterprises fix data quality problems without dropping a ton of cash, according to analysts.

  • As data demands speed up, information management tools pave road ahead

    New research shows data virtualization, data governance and master data management initiatives provide higher levels of satisfaction with data quality and data integration.

  • Bank of America IT exec: EIM drivers key to shaping project plans

    Enterprise information management programs should be molded around clear business objectives, says a senior information architect at Bank of America.

• Business Analytics

  • To glean value from ‘big data’ projects, it may take more than Hadoop

    Organizations will have to push beyond storage requirements and basic business intelligence tools to exploit big data, according to a Gartner analyst.

  • On the go the way to go? Answer lies with building mobile BI business case

    According to a recent Gartner survey, mobile technology is the second most important priority for CIOs in 2012, but building the business case is easier said than done.

  • Basic vs. complex: Companies walk the line on analytics best practices

    Businesses looking to build effective business intelligence and analytics programs have to toe the line between focusing on the basics and using new technology to build more complex BI systems.

• SAP

  • SAP builds the case for HANA in-memory bench with new apps, customers

    After talking up its mobile and cloud road maps, SAP chose last day of the SapphireNow conference to talk about the third part of the equation it’s basing its future success on: HANA, and in-memory technology.

  • SuccessFactors now leading SAP cloud strategy, but questions remain

    SAP has placed a huge bet on a new executive “born in the cloud” and on a company that will now guide both companies’ roadmap for existing products.

  • SAP tells of a future built on in-memory, mobile, and cloud

    SAP outlined its cloud strategy at the SapphireNow conference in Orlando. Are customers genuinely interested, or is it just SAP hype?

• SQL Server

  • More T-SQL statements in SQL Server 2012 you need to know about

    In this second article of a two-part series on new T-SQL statements, expert Serdar Yegulalp reviews command changes to sequencing, date values and more.

  • Three new T-SQL commands in SQL Server 2012 to make your life easier

    Each release of SQL Server brings changes to Transact SQL. In this tip, expert Serdar Yegulalp gives a walkthrough of tweaks like WITH RESULT SETS for renaming columns and THROW for catching errors.

  • Configuring SSRS the easy way

    Demystify the SQL Server Reporting Services (SSRS) configuration process. Get an overview of this tool and guidelines for how to set up each screen in SSRS from industry professional Roman Rehak.

• Java

  • FuseSource releases enterprise products at CamelOne 2012

    Here at CamelOne 2012, FuseSource is releasing two new products – Fuse ESB Enterprise 7.0 and Fuse MQ Enterprise 7.0.

  • Open source integration framework keeps motorists safe in the UK

    Automotive rescue and recovery services in the UK gained significant messaging advantages over the past few years. The new Automotive Network Services (ANS) system, built and maintained by Apex Networks, connects automotive clubs and insurance agencies with the mechanics, riggers, and first responders that provide emergency services to accidents and disabled vehicles.

  • Scaling up and scaling out to meet new business demands

    Scaling up and scaling out are two ways for growing businesses to increase productivity, but doing so blindly may be more costly than it's worth.

• Data Center

  • Optimize data center virtualization with strategic capacity planning

    Use strategic capacity planning to future-proof your data center's virtualization efficiency and make the most of server hardware.

  • Download the High Performance Comptuing E-Zine

    Get exclusive access to the High Performance Computing E-Zine, produced quarterly by the independent experts and analysts at SearchDataCenter.com

  • Changes to z/OS 1.13 help Java adoption and boost mainframe capacity

    Updates to zFS, better Java performance and tweaks to z/OSMF make IBM's z/OS 1.13 a solid release for developers.

• Content Management

  • Cimarex picks Accellion enterprise collaboration platform over SharePoint

    Cimarex Energy replaced its SharePoint system with Accellion’s enterprise collaboration platform for secure mobile file sharing and uses it to recruit graduate geology students.

  • Paper originals still needed when using a document management system?

    Get expert advice on whether it’s necessary to keep paper or other forms of original materials once a company implements an electronic document management system.

  • Out with old: Effective records management begins with losing useless data

    An effective records management program is one that not only knows what to keep – but which expired data can be removed to save time and money, said consultant Randolph Kahn.

• Financial Applications

  • SaaS HR, mobile analytics upends workforce management software market

    Nucleus Research names Ceridian, ADP among top performers and finds threats to legacy leader Kronos.

  • Basic planning, data cleansing seen as crucial to HR system integration

    Save time and money by establishing human resources processes and scrubbing data before embarking on a project to integrate systems, industry watchers say.

  • SaaS HCM growing, but some prefer customization of on-premises HCM

    As more companies consider taking human capital management to the cloud, analysts say the on-site version also has its benefits.

• About us
• Contact us
• Site index
• Privacy policy
• Advertisers
• Business partners
• Events
• Media kit
• TechTarget corporate site
• Reprints
• Site map