473,883 Members | 1,570 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 11984
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.c omwrote in message
news:10******** *************** ***********@b31 g2000prf.google groups.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
14527
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 submit_date BETWEEN '01/01/2005' AND '03/31/2005' I would like to dynamically generate the Quarter End and Quarter Beginning dates within my where clause based on the date that DTWS
1
4062
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 the quarter fo each region. To help enhance the report I would love to include a comparison to of current sales to previous quarter sales and a comparison of current sales to sales a year ago. I am having a devil of a time creating a query to...
2
3278
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 rst.FindFirst. I am using Access 2002: Function funAddBusinessDay(datStart As Date, intDayAdd As Integer) 'Adds the proper Business day skipping holidays and weekends 'Arvin Meyer 05/26/98 with modifications by G. J. Shears 6/16/98 'Need to add...
7
25999
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 efficient way for me to do this? -- Thanks, Sam
2
15290
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
1086
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 if a more complex macro is needed. Any help greatly appreciated. ChDir "L:\ftpin\cdsftp\SWAPS" Workbooks.OpenText FileName:="L:\ftpin\cdsftp\SWAPS\audtrail.FAI.1508.txt", _ origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited,...
0
2371
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 ASP.NET a DataGrid with Updatable rows based on a date retrieved from a data table in SQL Server. Below is the design algorithm. 1. Retrieve the Max(rundate) From MyDataTable 2. If the current month -1 = rundate .month from step 1 then 2a. ...
2
7659
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 having difficulties finding the last day of the quarter.
3
3282
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 writing query expressions so any help would be appreciated. Thanks, Yappy
0
9932
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10728
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10405
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9558
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7959
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5782
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5980
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
4200
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.