Error 1263 from dbaccess

I just got this error: SELECT * FROM <tablename> WHERE <datetime year to second> >= TODAY-2 ORDER BY <datetime year to second># ^ # 1263: A field in a datetime or interval value is out of range or incorrect. # I took a look at all the values in the datetime field and they all look good from what I saw (I eyeballed the year to day portion and those were all valid - I suppose the hour to second portion of a record could be bad) I ran oncheck -cD and oncheck -cI on the table and oncheck -cc on the database and those were all good. What could be wrong? OS is AIX 4.3.3.0 Informix Dynamic Server Version 7.31.UD6 -- On-Line -- Up 2 days 04:04:52 -- 431632 Kbytes Thanks

Danny Wright wrote: > >I just got this error: > >SELECT * >FROM <tablename> >WHERE <datetime year to second> >= TODAY-2 >ORDER BY <datetime year to second>># ^ ># 1263: A field in a datetime or interval value is out of range or >incorrect. ># > >I took a look at all the values in the datetime field and they all look >good >from what I saw (I eyeballed the year to day portion and those were all >valid - I suppose the hour to second portion of a record could be bad) > > >I ran oncheck -cD and oncheck -cI on the table and oncheck -cc on the >database and those were all good. > >What could be wrong? > >OS is AIX 4.3.3.0 >Informix Dynamic Server Version 7.31.UD6 -- On-Line -- Up 2 days >04:04:52 -- 431632 Kbytes I think the -1263 is misleading. I would have expected -1260, perhaps. You are comparing a DATETIME to DATE. There are a couple of ways to work around that. 1. Use CURRENT (DATETIME) rather than TODAY (DATE): SELECT * FROM <tablename> WHERE <datetime year to second> >= CURRENT-2 UNITS DAY ORDER BY <datetime year to second> 2. Use EXTEND to adjust the precision of a DATE (TODAY) to a DATETIME value: SELECT * FROM <tablename> WHERE <datetime year to second> >= EXTEND(TODAY - 2, YEAR TO SECOND) ORDER BY <datetime year to second> 3. Use the DATE function to return a DATE from your DATETIME value: SELECT * FROM <tablename> WHERE DATE(<datetime year to second>) >= TODAY -2 ORDER BY <datetime year to second> There are probably other options, but these three would likely be the most common. Depending on how precise you need to be when comparing that stored DATETIME value to a point in time two days ago, one solution may be more appropriate than another. (Is the hour, minutes, and seconds important to you?) See the IBM Informix Guide to SQL: Syntax for more information about these functions. -- June Hunt