Re: Helllppppp on Date operations

Fernando.Collalti@orbit.net ("Collalti, Fernando") wrote: >Hello(or Help?), > Is the first time I face DATE operations. My "problem" is >the following: > columnA is DATE > columnB is DATE > columnC is INTEGER > columnA: is an ending date(a date when my subscription will end) > columnB: is the day that I started the subscription > columnC: is the duration of my subscription(expessed in months) > I need to SET the value of columnA equal to: > columnB to end of the month + columnC > Example: > columnA= null > columnB= 15/01/1996 > columnC= 2 (2 months) > The value of columnA should be the following: > step1: bring 15/01/1996 to the end of the month. So to 31/01/1996 > step2: from the end of the month of columnA, add 2 months(value of >columnC) > 1-My tool is SQL on INFORMIX-SE 5.04 and INFORMIX-On Line 7.1. Can my >problem >be solved just with SQL? I think so - but it most probably involves multiple updates of a temporary table. > 2- Is yes, could you give me an example of the statement? SELECT key, (MDY(MONTH(columnB)+1+columnC, 01, YEAR(columnB)) - 1) exp_date FROM table WHERE MONTH(columnB)+columnC < 12 INTO TEMP temp_table WITH NO LOG; INSERT INTO temp_table SELECT key, (MDY(MONTH(columnB)-11+columnC, 01, YEAR(columnB)+1) - 1) FROM table WHERE MONTH(columnB)+columnC >= 12; UPDATE table SET columnA = (SELECT exp_date FROM temp_table WHERE temp_table.key = table.key); (Totally untested of course). >Thanks in advance to all the persons that will answer my questions. In this >case, I really need help. Cheers, Brent Jackson. brent@hypercom.co.nz Phone : 64-9-3603593 Fax : 64-9-3602840