SQL for Previous Quarter Start and end Dates

Posted on 2011-04-22
Last Modified: 2012-06-22

  Can someone kindly give me SQL for calculating previous quarter first and last dates for a given date?
Question by:pvsbandi
    LVL 37

    Expert Comment

    lets go through the logic
    basically if you have a date X,
    to get the previous quarter you would like to :
    1) check X mod 3 - will return number of monthes into the current quarter (sor for example, for october it's 1, for september it's 0)
    2) subtract the anwer of 1 + plus 3 months
    3) subtract the number of days into the current month


    select date_value - month(date_value)%3 month - 3 month - day(date_value) days as first_day_of_prev_quarter,
     date_value - month(date_value)%3 month - day(date_value) days -1 day as last_day_of_prev_quarter
    from your_table

    Author Comment

    Thanks! But i'm getting an error near %

    Author Comment

    OK.. i have changed the logic to below:
    select current date - mod(month(current date),3) months - 3 months - day(current date) days as first_day_of_prev_quarter,
     current date - mod(month(current date),3) months - day(current date) days -1 days as last_day_of_prev_quarter

    Open in new window

    But what i get as the result set is :  2010-11-30      2011-02-27

    But i was expecting 01/01/2011 and 03/31/2011.
    Can you please advise?
    LVL 37

    Accepted Solution

    it seems that my logic was wrong,
    this should work then

    select current date - (mod(month(current date)-1, 3) months - 3 months - day(current date) days +1 day as first_day_of_prev_quarter,
     current date - (mod(month(current date)-1, 3) months - day(current date) days  as last_day_of_prev_quarter

    Author Closing Comment

    works now :).. thanks!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now