Rolling 3 months in advance,

Beebo · #1 (**permalink**) 07-07-09, 18:42

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.

bigcalm · #2 (**permalink**) 07-08-09, 03:15

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

Beebo · #3 (**permalink**) 07-08-09, 09:49

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

bigcalm · #4 (**permalink**) 07-08-09, 10:41

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.

Beebo · #5 (**permalink**) 07-08-09, 10:57

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.

bigcalm · #6 (**permalink**) 07-08-09, 11:24

Hrm, post the full sql if you can - it might make it a bit more obvious if there's a problem.

Tyveleyn · #7 (**permalink**) 07-08-09, 16:00

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

Internet Services

Web Development

Digital Marketing

Consumer Tech