473,372 Members | 1,033 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

Get the last day of previous business quarter

Is there a way to get the last day of the previous business quarter
from DB2?

For 10/21/2008 the day would be 9/30/2008.

Thanks.
Oct 22 '08 #1
4 11896
What is the "business quarter"?
"last day of previous quarter" would be calculated as following.
------------------------- Commands Entered -------------------------
SELECT d
, d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) * 3) MONTHS
AS "last day of previous quarter"
FROM (VALUES
CURRENT DATE
, DATE('2008-01-01')
, DATE('2008-03-31')
, DATE('2008-04-01')
, DATE('2008-07-31')
, DATE('2008-09-30')
, DATE('2008-10-01')
, DATE('2008-12-31')
) D(d)
;
--------------------------------------------------------------------

D last day of previous quarter
---------- ----------------------------
2008-10-22 2008-09-30
2008-01-01 2007-12-31
2008-03-31 2007-12-31
2008-04-01 2008-03-31
2008-07-31 2008-06-30
2008-09-30 2008-06-30
2008-10-01 2008-09-30
2008-12-31 2008-09-30

8 record(s) selected.

Oct 22 '08 #2
Terrific! This is just what I wanted. Thanks.
Oct 23 '08 #3

<gi*******************@yahoo.comwrote in message
news:10**********************************@b31g2000 prf.googlegroups.com...
Is there a way to get the last day of the previous business quarter
from DB2?

For 10/21/2008 the day would be 9/30/2008.

Thanks.
Why would you need to _calculate_ these values? Assuming we define these
quarters in the traditional fashion:
- the last day of the 1st quarter is March 31
- 2nd June 30
- 3rd September 30
- 4th December 31

This is true for any year without exception, as far as I know.

--
Rhino
Oct 25 '08 #4
Why would you need to _calculate_ these values?

Because, expression must be get longer and complex.
If you know shorter expression than one in the following example,
please show me that.

------------------------------ Commands Entered
------------------------------
SELECT d
, DATE(
SUBSTR(CHAR(d + (SIGN(QUARTER(d)-1)-1) YEAR),1,5)
|| CASE QUARTER(d)
WHEN 1 THEN '12-31'
WHEN 2 THEN '03-31'
WHEN 3 THEN '06-30'
WHEN 4 THEN '09-30'
END
) AS "last day of previous quarter 1"
, d - DAYOFYEAR(d) DAYS + ((QUARTER(d) - 1) * 3) MONTHS
AS "last day of previous quarter 2"
FROM (VALUES
CURRENT DATE
, DATE('2008-01-01')
, DATE('2008-03-31')
, DATE('2008-04-01')
, DATE('2008-07-31')
, DATE('2008-09-30')
, DATE('2008-10-01')
, DATE('2008-12-31')
) D(d)
;
------------------------------------------------------------------------------

D last day of previous quarter 1 last day of previous quarter
2
---------- ------------------------------
------------------------------
2008-10-29 2008-09-30
2008-09-30
2008-01-01 2007-12-31
2007-12-31
2008-03-31 2007-12-31
2007-12-31
2008-04-01 2008-03-31
2008-03-31
2008-07-31 2008-06-30
2008-06-30
2008-09-30 2008-06-30
2008-06-30
2008-10-01 2008-09-30
2008-09-30
2008-12-31 2008-09-30
2008-09-30

8 record(s) selected.

Oct 29 '08 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: Matt | last post by:
Hello, I have a query that I would like to schedule in DTS. The criteria of this query checks for records in the table that are within the current quarter. Here is what I have. WHERE...
1
by: Terencetrent | last post by:
I have created a query that examines qarterly sales for 5 regions in the country. The query contains data for the past 6 quarters for each region and calculates the perecentage of total sales for...
2
by: Mitchell | last post by:
I am trying calculate the previous business day from today. I am trying to use the code below i found on this site, but keep getting error message when i try and compile, does not recognize...
7
by: Sam | last post by:
Hi, I use C# in my ASP.NET projects. Here's what I need to do: I want to add x business days to a given date i.e. add 12 business days to today's date. What is the best, fastest and most...
2
by: schapopa | last post by:
Hi, I want to create query where I could group records by quarters, and get the last record in each group. e.g Create Table MyTable ( Value , date )
0
by: jalo | last post by:
I need help with this Macro. At the moment I can only use it for the one day specified ie the 1508 but need it to pick up the previous business day. Don't know if there is a simple remedy for this or...
0
by: marlberg | last post by:
Platform: Windows2000, WindowsXP, Windows Vista, etc Language: C#, ASP.NET Pre-compiled Libraries: Enterprise Library 3.0 full I have a requirement to implement in and display in C# and...
2
benchpolo
by: benchpolo | last post by:
First Day of the QUARTER select DATEADD(qq, DATEDIFF(qq,0,getdate()), 0) Question: How do I get the last DAY of the QUARTER? For example: 1st Quarter is 01/01/2008 to 03/31/2008. I am...
3
by: yappy77 | last post by:
I need an expression that will calculate the last day of a quarter based on a specific date. For example: Expiration date = 1/15/11; Last day of quarter = 3/31/11. I am not that great at...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.