There's TODAY and you can apply modifiers to that, not sure if that helps, eg.
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?
where connect_date >= TODAY - 3 units month
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
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.
Hi, for calculating the last day of a given month I use this stored function with a DBDATE value of DMY4-:
CREATE FUNCTION "informix".endofmonth(dt DATE DEFAULT NULL)
IF dt IS NULL THEN
LET dt = TODAY;
LET dt = date(extend(dt, YEAR TO MONTH) +1 UNITS MONTH) -1 UNITS DAY;
DOCUMENT 'Function to return the last day of the current or given month';
This way I could use it in a query like:
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.