# Thread: Rolling 3 months in advance,

1. Registered User
Join Date
May 2009
Posts
19

## Unanswered: Rolling 3 months in advance,

All,

Is there a date parameter in InformiX that would give you a rolling 3 months? I'm trying to compensate for certain maturities coming due.

2. Registered User
Join Date
Jul 2009
Posts
37
There's TODAY and you can apply modifiers to that, not sure if that helps, eg.

select *
from active
where connect_date >= TODAY - 120

(gets all records from active where connect_date is in the last 120 days).

You can also use the next example, but when I've tried to use it in the past it's been basically broken (you can end up with null dates when it's not valid - eg subtracting 3 months from 31st of May gives 31st of February - which it'll convert into a null). Anyone here know if this has been fixed?

select *
from active
where connect_date >= TODAY - 3 units month

3. Registered User
Join Date
May 2009
Posts
19
BigCalm,

Your name speaks volumes. I wish I could state the same!

Thanks for your help, but I probably need to clarify. I'm looking for maturities in advance, so that we can act on them accordingly. Basically, I really need to know the logic for looking a month in advance.

For instance, the following piece of code will give me the first day of the next month (in this case August 09).

MDY(MONTH(TODAY), 1, YEAR(TODAY)) + 1 UNITS MONTH

What I can't figure out is how to determine the last day of next month???

I'm looking to create some kind of between statement. The following statement will give me last month's detail, but I can't figure out how to manipulate it to give me next months....

between MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1 UNITS MONTH AND
MDY(MONTH(TODAY), 1, YEAR(TODAY)) - 1

4. Registered User
Join Date
Jul 2009
Posts
37
MDY(MONTH(TODAY), 1, YEAR(TODAY)) + 1 UNITS MONTH

What I can't figure out is how to determine the last day of next month???

---
You're going to kick yourself...

MDY(MONTH(TODAY), 1, YEAR(TODAY)) + 2 UNITS MONTH - 1 UNITS DAY

ie. Add two months to first day of the month and take off one day to get the last day of the next month.

5. Registered User
Join Date
May 2009
Posts
19
BigCalm,

Thanks for the help...that did work. The only problem is that it came back blank. I realized the reason is that I didn't have any information for the last day of the month, so it brought back nothing for the whole month..even though there was information there.

6. Registered User
Join Date
Jul 2009
Posts
37
Hrm, post the full sql if you can - it might make it a bit more obvious if there's a problem.

7. Registered User
Join Date
Aug 2006
Location
The Netherlands
Posts
248
Hi, for calculating the last day of a given month I use this stored function with a DBDATE value of DMY4-:
Code:
```CREATE FUNCTION "informix".endofmonth(dt DATE DEFAULT NULL)
RETURNING DATE;

IF dt IS NULL THEN
LET dt = TODAY;
END IF
LET dt = date(extend(dt, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;
RETURN dt;

END FUNCTION
DOCUMENT 'Function to return the last day of the current or given month';```
This way I could use it in a query like:
Code:
`BETWEEN endofmonth() +1 UNITS DAY AND endofmonth(date(extend(today, YEAR TO MONTH) +1 UNITS MONTH))`
I've been used to the extend function for years so I don't use mdy as you can see. But I don't know if this way of date calculation solves your problem, I would think not.
Maybe your problems have somthing to do with the DATE format according to the DBDATE parameter being different from the mdy output but I don't consider that very likely. Check it out and also try an alternative way, like this one.

Good luck,
Hans
Last edited by Tyveleyn; 07-09-09 at 02:51.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•