Results 1 to 7 of 7
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    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. #7
    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
  •