Get the last day of previous business quarter

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gimme_this_gimme_that@yahoo.com - 22 Oct 2008 02:42 GMT

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.

Reply to this Message

Tonkuma - 22 Oct 2008 04:01 GMT

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.

Reply to this Message

gimme_this_gimme_that@yahoo.com - 23 Oct 2008 03:00 GMT

Terrific! This is just what I wanted. Thanks.

Reply to this Message

rhino - 25 Oct 2008 16:36 GMT

> 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

Reply to this Message

Tonkuma - 29 Oct 2008 12:45 GMT

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

Reply to this Message