Re: HELP - How to Do this Query?

On Mon, 13 Oct 1997, Art S. Kagel wrote: > Yan Liu wrote: > > We have a DATE field of a table. Now I need to select all the records > > around a day. For example between "97/10/10" - 10 days and "97/10/10" +10 days. > > How shall I write the WHERE statement? > > > > I know it would be easy for a datetime field by using 10 UNITS DAY. > > But what to do for DATE field? Any body have any ideas? Thanks! > > What are you using for a front-end tool? ESQL/C? 4GL? DBACCESS? A key question... > The answer depends. In ESQL/C convert the string date to a date > variable which is stored as an integer number of days since 12/30/1899. > Then just copy basedate-10 into lowerbound and basedate+10 into > upperbound and: > > select .... > from ... > where datefield between :lowerbound and :upperbound; So far, so good. But ESQL/C is the hardest solution... > In 4GL you can do the same but you need to write a 4GL callable "C" > function to do the arithmetic. NO! FUNCTION somefunc(d) DEFINE d DATE DEFINE hi DATE DEFINE lo DATE ... LET hi = d + 10 LET lo = d - 10 ... END FUNCTION > In DBACCESS you must do the date calculations yourself. SELECT ... FROM ... WHERE DateField BETWEEN DATE("1997/10/12") - 10 AND DATE("1997/10/12") + 10 Eg: SELECT tabname FROM SysTables WHERE Created BETWEEN TODAY - 365 AND TODAY - 10; Yours, Jonathan Leffler (johnl@informix.com) #include <witticism.h>