Link to home
Start Free TrialLog in
Avatar of saved4use
saved4useFlag for United States of America

asked on

DB2 date function between specific dates next month

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.
Avatar of sathyaram_s
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Kent Olsen
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,
Avatar of saved4use


Perfect solution! Exactly what I needed.
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