Re: Interval Question

> Good morning everyone, Hi, see below. > I have been tasked with converting a SQL Server (Vers. 7) database to > Informix. The data conversion was sloppy but is now complete. There > are many stored procedures and I am rewriting them for Informix. One of > them has an expression, as part of the where clause, that specifies to > only get rows where a timestamp column is less than the current time > plus a time zone adjustment value. The original SQL Server expression > is: > > ... and ra.endtimestamp < dateadd(hh, w.timezoneadjustment, > getdate()) ... > > I thought that the equivalent Informix SPL expression would be: > > ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + > INTERVAL (w.timezoneadjustment) hour to hour ... I think the syntax you are trying to use is only for INTERVAL constants. Try an cast: ... and ra.endtimestamp < datetime(CURRENT) year to fraction(3) + w.timezoneadjustment CAST AS INTERVAL hour(2) to hour ... Or , why not just ALTER the timezoneadjustment column to a type INTERVAL HOUR(2) TO HOUR and then you can use it directly? > but that reutrns a syntax error. > > Another related question: Will the CURRENT keyword return the current > system time or only the time that was current at compilation? If it is > the latter, does someone have a handy way to get the current system time > in a stored procedure (a system call perhaps?). In a procedure or function CURRENT returns the time the procedure was called not the time it was compiled (nor the current time so getting the value of CURRENT in a loop always returns the same value). Art S. Kagel > Here is some pertinent information: > HP-UX 11.0 > IDS 9.21.FC4XQ > ra.endtimestamp is datetime year to fraction(3) > w.timezoneadjustment is integer > > Does anyone know if what I am doing is allowed in stored procedures. Am > I properly converting this expression? > > Any help would be appreciated. > > Thanks > > Rob Schmitz > 913-345-6281 > Rob.B.Schmitz@mail.sprint.com