DB2 date function between specific dates next month

saved4use
saved4use used Ask the Experts™
on
Every month on the 23rd, I'll run a query whereupon I want to pick up assets that mature between  the 14th of next month and the last day of next month. *For 5/23, it's going to be btwn 6/14 and 6/30.
What date function do I need to use?

<= CURRENT_DATE  + 40 days doesn't work as it still includes 5/23, 5/31, etc.

Thank you for your help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
with temp(dt) as (values (date('2012-04-23')), (date('2012-05-23')), (date('2012-03-23')), (date('2012-02-23')), (date('2012-01-23')), (date('2012-06-23')), (date('2012-07-23')), (date('2012-08-23')), (date('2012-09-23')), (date('2012-10-23')), (date('2012-11-23')), (date('2012-12-23')) ) select dt,dt-(day(dt)-1-13) days + 1 month ,dt-(day(dt) days)+2 months  from temp

Open in new window


DT         2          3
---------- ---------- ----------
04/23/2012 05/14/2012 05/31/2012
05/23/2012 06/14/2012 06/30/2012
03/23/2012 04/14/2012 04/29/2012
02/23/2012 03/14/2012 03/31/2012
01/23/2012 02/14/2012 02/29/2012
06/23/2012 07/14/2012 07/31/2012
07/23/2012 08/14/2012 08/30/2012
08/23/2012 09/14/2012 09/30/2012
09/23/2012 10/14/2012 10/31/2012
10/23/2012 11/14/2012 11/30/2012
11/23/2012 12/14/2012 12/31/2012
12/23/2012 01/14/2013 01/30/2013

  12 record(s) selected.
Hi Saved,

If you can tolerate a query that's a little flexible, here's one that you can run any time during the month and it will pick the items in that range for next month.

SELECT * FROM sometable
WHERE current_date - (day (current_date - 1 + 13)) days + 1 month <= mature_date
  AND current_date - (day (current_date) -1) days + 2 months > mature_date

Open in new window



Good Luck,
Kent

Author

Commented:
Perfect solution! Exactly what I needed.
Thanks!
When looking at the output again, I see a mistake in calculating the last day of the month

It should be

dt-(day(dt)-1) days+2 months - 1 day

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial