[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
Medium Priority
3,565 Views
Hi,

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

LVL 37

Expert Comment

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

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

Author Comment

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
``````

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.
0

LVL 37

Accepted Solution

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

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

## Featured Post

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
Course of the Month20 days, 5 hours left to enroll