[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL for Previous Quarter Start and end Dates

Posted on 2011-04-22
5
Medium Priority
?
3,565 Views
Last Modified: 2012-06-22
Hi,

  Can someone kindly give me SQL for calculating previous quarter first and last dates for a given date?
0
Comment
Question by:pvsbandi
  • 3
  • 2
5 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 35449420
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

so,

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
0
 

Author Comment

by:pvsbandi
ID: 35449459
Thanks! But i'm getting an error near %
0
 

Author Comment

by:pvsbandi
ID: 35449609
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
     FROM SYSIBM.SYSDUMMY1

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?
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 2000 total points
ID: 35449691
well,
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
     FROM SYSIBM.SYSDUMMY1
0
 

Author Closing Comment

by:pvsbandi
ID: 35449778
works now :).. thanks!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
Suggested Courses

873 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