473,372 Members | 1,033 Online

# 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
"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
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
- 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,

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