Re: INTERVAL FUNCTION

On Wed, 23 Jul 2003 12:44:47 -0400, tomL wrote: > Hello, > I am unsure of why this function behaves like it does (or more > accurately, what the sql means)? > create_date is a DATETIME YEAR TO SECOND > > select * from request where> create_date <= EXTEND(current, year to second) - interval (99) second to > second > > why does the above statement work but when i replace 99 with 100, it > fails? Because you did not specify the resolution/size of the seconds interval which defaults to 2 digits only. > as i increase the number of seconds in the interval, i am required to > change the sql to this: > > select * from request where> create_date <= EXTEND(current, year to second) - interval (18000) second > (5) to second > > what does the (5) mean and why do i have to increase it as i increase > the interval seconds? the above sql will fail if it is a (4) instead of > a (5). It specifies the number of digits of precision or the size of the highest resolution field in the interval (ie if it was INTERVAL() YEAR TO SECOND you would have to make it INTERVAL() YEAR(4) TO SECOND to handle 1000 year intervals). This is because under the hood an INTERVAL is stored in the database as a DECIMAL type with the upper and lower resolutions encoded in the syscolumns:collength field. So if you do not specify a size for the upper resolution the data structure set up for it can only hold 2 digits. Art S. Kagel > Thanks in advance, > Tom