Datetime and Interval

Hi, This the second time that i ask about this argument: I've a problem with a store procedure that work with Datetime: If I've this situation: CREATE PROCEDURE PROC_NAME(Data DATETIME YEAR TO FRACTION) DEFINE pippo INTEGER; .... .... LET pippo = Data - CURRENT; .... .... END PROCEDURE; This procedure was successfull compilate but when it run the result is an error of conversion type !!!! It's not possible convert between an interval to integer ??? My interval is MINUTE TO MINUTE so i think that the conversion is rigth. Please help me .... Thanks Max

Massimiliano Panella wrote: > This the second time that i ask about this argument: > I've a problem with a store procedure that work with Datetime: > If I've this situation: > CREATE PROCEDURE PROC_NAME(Data DATETIME YEAR TO FRACTION) > DEFINE pippo INTEGER;> .... > .... > LET pippo = Data - CURRENT; > .... > .... > END PROCEDURE; > This procedure was successfull compilate but when it run > the result is an error of conversion type !!!! Yes. That's the way it works, or doesn't work. > It's not possible convert between an interval to integer ??? Absolutely not. > My interval is MINUTE TO MINUTE There's no explicit interval in your code, so the difference between 'data' and CURRENT is an INTERVAL DAY(n) TO FRACTION, where the n could be 9 or some slightly smaller value. But even when you do have an INTERVAL MINUTE TO MINUTE, there is still no built-in conversion from the INTERVAL type to an INTEGER. > so i think that the conversion is right. FAQ time, again. I'm not sure whether it is in the FAQ or not, but... If you want to convert between INTERVAL and INTEGER, go via a string. You say you want your INTERVAL to be the number of minutes between the given value and the current value, so use: CREATE PROCEDURE PROC_NAME(Data DATETIME YEAR TO FRACTION); DEFINE i INTERVAL MINUTE(9) TO MINUTE; DEFINE s CHAR(10); DEFINE pippo INTEGER; ... LET i = EXTEND(Data, YEAR TO MINUTE) - CURRENT YEAR TO MINUTE; LET s = i; LET pippo = s; ... END PROCEDURE; -- code not syntax checked! The basic principles are: 1. Use an explicit INTERVAL variable to control the result. 2. Convert to string and thence to integer.

Just as an aside... Why does Informix use a DATETIME type instead of the the SQL TIMESTAMP type? Massimiliano Panella wrote in message <36DD6F77.F3CC6949@metoda.it>... >Hi, >This the second time that i ask about this argument: >I've a problem with a store procedure that work with >Datetime: >If I've this situation: >CREATE PROCEDURE PROC_NAME(Data DATETIME YEAR TO FRACTION) > DEFINE pippo INTEGER;> .... > .... > LET pippo = Data - CURRENT; > .... > .... >END PROCEDURE; >This procedure was successfull compilate but when it run >the result is an error of conversion type !!!! >It's not possible convert between an interval to integer ??? >My interval is MINUTE TO MINUTE so i think that the conversion >is rigth. > >Please help me .... >Thanks Max >

Peter Howe wrote: > Just as an aside... > > Why does Informix use a DATETIME type instead of the the > SQL TIMESTAMP type? AFAIK, Informix implemented a draft version of the SQL-92 standard DATETIME handling, and the draft changed before the standard was finalized, but Informix had released the product. TIMESTAMP corresponds to DATETIME YEAR TO SECOND. > Massimiliano Panella wrote in message <36DD6F77.F3CC6949@metoda.it>... > >I've a problem with a store procedure that work with Datetime: > >If I've this situation: > >CREATE PROCEDURE PROC_NAME(Data DATETIME YEAR TO FRACTION) > > DEFINE pippo INTEGER;> > .... > > .... > > LET pippo = Data - CURRENT; > > .... > > .... > >END PROCEDURE; > >This procedure was successfull compilate but when it run > >the result is an error of conversion type !!!! > >It's not possible convert between an interval to integer ??? > >[...] -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>

Jonathan Leffler wrote: > Peter Howe wrote: > > Just as an aside... > > > > Why does Informix use a DATETIME type instead of the the > > SQL TIMESTAMP type? > > AFAIK, Informix implemented a draft version of the SQL-92 standard > DATETIME handling, and the draft changed before the standard was > finalized, but Informix had released the product. > > TIMESTAMP corresponds to DATETIME YEAR TO SECOND. Tanks Jonathan,Peter but I've the problem with a DB that now running for an application and my application (taking information from the DB) need know the number of minutes that was spent from DATA1 to DATA2 two DATETIME YEAR TO FRACTION My problem is it's possible that the difference from two DATE MUST BE AN INTERVALL and not an integer ???? Can I resolve this problem with SPL ?? Massimiliano Panella Tank You

TOMMY wrote: > > Jonathan Leffler wrote: > > > Peter Howe wrote: > > > Just as an aside... > > > > > > Why does Informix use a DATETIME type instead of the the > > > SQL TIMESTAMP type? > > > > AFAIK, Informix implemented a draft version of the SQL-92 standard > > DATETIME handling, and the draft changed before the standard was > > finalized, but Informix had released the product. > > > > TIMESTAMP corresponds to DATETIME YEAR TO SECOND. > > Tanks Jonathan,Peter but I've the problem with a DB that now running for an > application and my application (taking information from the DB) need know > the number of minutes > that was spent from DATA1 to DATA2 two DATETIME YEAR TO FRACTION > My problem is it's possible that the difference from two DATE MUST BE AN > INTERVALL > and not an integer ???? > Can I resolve this problem with SPL ?? Yes in SPL you can calculate the interval minute(3) to minute and then assign the result to an integer and return that integer. Jonathan posted such a beasty about a month ago for a similar question. Search the IIUG Archive of CDI if you have any problem. Art S. Kagel