Re: datetime - datetime = interval but it is always null
Posted in 1996
Topics: Stored Procedures & SPL
Try adding WHENEVER ANY ERROR STOP to the top of the code. It will probably reveal an expression error for the exp_int calculation. Then try using the following assignment which should, I think, produce the correct answer. If it doesn't, then you've probably found a bug... LET exp_int = EXTEND(e_date, YEAR TO MONTH) - EXTEND(s_date, YEAR TO MONTH) Now, separately from that, you may need to convert exp_int into a number of months for the '> 60 units month' comparison. See the code below which works with p-code 6.04.UC1... Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h> MAIN DEFINE s_date DATETIME YEAR TO MINUTE DEFINE e_date DATETIME YEAR TO MINUTE DEFINE c_date DATETIME YEAR TO MINUTE DEFINE exp_int INTERVAL YEAR (4) TO MONTH DEFINE num_int INTEGER DEFINE mm CHAR(8) -- Gruesome WHENEVER ANY ERROR STOP LET s_date = "2015-11-11 00:00" LET e_date = "2022-11-10 00:00" LET c_date = "1998-09-28 00:00" DISPLAY "s_date :", s_date DISPLAY "e_date :", e_date DISPLAY "c_date :", c_date LET exp_int = EXTEND(e_date, YEAR TO MONTH) - EXTEND(s_date, YEAR TO MONTH) IF exp_int IS NOT NULL THEN DISPLAY exp_int, " -- is not null" END IF -- This code is truly awesomely gruesome! -- Convert interval to string, and then treat subsections of string as -- integers! Allow for sign on interval... LET mm = exp_int LET num_int = mm[2,5] * 12 + mm[7,8] DISPLAY "Interval is: ", exp_int, " -- ", num_int IF (num_int < 60) THEN DISPLAY "Interval is less than 60 months" ELSE DISPLAY "Interval is more than 60 months" END IF END MAIN }From: Kirk Waterhouse <kirk.waterhouse@usat.mhs.ciba.com> }Date: Wed, 18 Dec 1996 16:32:59 -0800 }X-Informix-List-Id: <news.31813> } }I am trying to compare the expiration date of a unit of inventory with }the location I would like to put it in. If there is more than 60 months }difference I want to put it somewhere else. } }Take a look at this sample code: } }database blah } }main } } define s_date datetime year to minute } define e_date datetime year to minute } define c_date datetime year to minute } define cur_time datetime hour to fraction(3) } define exp_int interval year (4) to month } define num_int integer } define dt_str char(80) } define i integer } define j integer } } let s_date = "2015-11-11 00:00" } let e_date = "2022-11-10 00:00" } let c_date = "1998-09-28 00:00" } let cur_time = current } } display "s_date :", s_date } display "e_date :", e_date } display "c_date :", c_date } } let exp_int = (e_date - s_date) } if exp_int is not null ## I think it is always }null } then } display "it is not null" } end if } let num_int = exp_int } let dt_str = exp_int } display "Interval is: ", exp_int using " ###", "-", num_int, }"-",dt_str } --if (s_date > "2015-01-01 00:00" ## This works ok but YUK } --and e_date > "2015-01-01 00:00") } if ((e_date - s_date) < 60 units month) ## The interval never fits } then } display "Interval fit" } else } display "Interval did not fit" } end if } }end main } }I would hate to hard code the dates in and it requires more programming. }HELP!! } }Thanks in advance