RE: Number of hours from interval, how?

haven't tested this at all, but try the following select (today-date(mydatetime))*24 +(current hour to hour -extend(mydatetime hour to hour)*1 from mytable where your_condition the ratio being that both the date-date and the interval*number yeld an integer. Of course, AFAIK, the engine could consider the (datetime-datetime)*integer as not being equivalent to interval*integer... Should that not work, and If all you are interested is a prioritized list, the you could consider something along these lines select (today-date(mydatetime))*24, +(current hour to hour -extend(mydatetime hour to hour)*1 from mytable where your condition order by 1 desc, 2 desc Other than that, forget creating a view and resort to using 4gl. You could convert the interval to a string, extract the day and hour part from that, convert them to integers and do the multiplication (easier done than said :-) HTH, Marco ____________________________________________________________________________ Marco Greco <marcog@ctonline.it> rem radioterapia 39 95 447828 fax 446558 Informix-FAQ http://www.iiug.org 4glWorks http://www.ctonline.it/~marcog --- On Tue, 20 May 1997 20:18:21 GMT Gottfried Schwieters <schwiete@quantum.de> wrote: Hello, maybe the following is a FAQ, but I couldn't find something about it in that document, so here goes: Summary: How do I get the number of hours from a datetime difference (an interval) as integer value? Longer Explanation: Example Table: CREATE TABLE mytable ( mykey CHAR(10), mydatetime DATETIME YEAR TO FRACTION(3) ); The value of mydatetime is somewhere in the future. I want to compute the number of hours this point of time is away from now. I can do something like SELECT EXTEND (mydatetime, YEAR TO HOUR) - CURRENT YEAR TO HOUR FROM mytable WHERE mykey = 'ABC'; This gives an INTERVAL DAY TO HOUR(?) value of let's say '10 13', saying the difference is ten days and 13 hours. That is the 'nearest' I could get so far. But I wish to have the number of hours (in this example 253). How To? The value is needed for further computing in a function that computes kind of a 'most urgent' entry. A computation like <constant weight factor> * <number of hours left> is part of this function (amongst others). I like to create a view which has a column that gives me this number of hours as an integer value. I also tried writing a SPL-Procedure to fill this column but with no success. Engine is SE 7.22. Any help is appreciated. Gottfried. -- Gottfried Schwieters ** Quantum GmbH ** 44137 Dortmund ** -----------------End of Original Message-----------------