469,323 Members | 1,641 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

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 11134
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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Mitchell | last post: by
7 posts views Thread by Sam | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.