No of Days between two DATETIME fields

Thanks for your suggestion. Anyway i could fix the problem 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>The above qry works fine if i declare a variable of type INTERVAL DAY TO DAY as return variable in SPL. and Select current,timestamp,date(current) - date(timestamp) from requestapproval where <condn> works if i declare as an INTEGER variable. Thanks Again Kalpana Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<oqPcb.4573$NX3.3600@newsread3.news.pas.earthlink.net>... > 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