No of Days between two DATETIME fields

Hi ALL, I want to get the no of days between 2 datetime fields. Both fields are of type DATETIME YEAR TO SECOND. The following query executes and gives correct result in SQL editor, but it gives an error from SPL as follows (-1265 overflow occured on datetime or interval operation) Select requestid, EXTEND(dtfield ,month to day), EXTEND (timestamp, month to day) ,EXTEND(dtfield ,month to day)- EXTEND (timestamp, month to day) AS no_of_days from requestapproval where <condn> and also Select current,timestamp,date(current) - date(timestamp) from requestapproval where <condn>works fine in sql but from SPL gives an error . As per my understanding if we subtract 2 datetime values it returns an INTERVAL, i have declared variable of type v_no_of_days INTERVAL DAY TO HOUR; As i have not used this interval type, could anybody suggest me , by declaring as what it will give a correct result. and also can i return INTERVAL from SPL , if so what is the interval format Any help highly appreciated. Regards Kalpana Pai

KalpanaPai wrote: > I want to get the no of days between 2 datetime fields. > Both fields are of type DATETIME YEAR TO SECOND. > > The following query executes and gives correct result in SQL editor, > but it gives an error from SPL as follows > > (-1265 overflow occured on datetime or interval operation) > > Select requestid, EXTEND(dtfield ,month to day), > EXTEND (timestamp, month to day) ,> EXTEND(dtfield ,month to day)- EXTEND (timestamp, month to day) AS > no_of_days > from requestapproval where <condn> Odd - so you don't care about whether one date is in 2000 and the other in 2003? That's a different query than the one you stated that you're asking. Try: SELECT INTERVAL(0) DAY(9) TO DAY + (dtfield - timestamp) AS no_of_days FROM requestapproval WHERE <condn> You might get away with 0 UNITS DAY but I would not trust it without testing on extreme values. > and also > Select current,timestamp,date(current) - date(timestamp) > from requestapproval where <condn>> works fine in sql but from SPL gives an error . > > As per my understanding if we subtract 2 datetime values it returns an > INTERVAL, > i have declared variable of type > v_no_of_days INTERVAL DAY TO HOUR; Implicitly, that's INTERVAL DAY(2) TO HOUR, and you have a difference of more than 3 1/3 months, at a guess. And you said you wanted the answer in days, not days and hours. You can modify the type of the interval constant to the type you really want, and then it should work. You might still need to worry about what happens with: 2003-09-01 03:04:50 minus 2003-07-09 06:07:00 > As i have not used this interval type, could anybody suggest me , by > declaring as what it will give a correct result. and also can i return > INTERVAL from SPL , if so what is the interval format > > Any help highly appreciated. > Regards > Kalpana Pai -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/