Help w/ Date field formating in WHERE clause

Hi, I need to create a query in which the where clause's field is equal to the previous day's date formatted in YYMMDD format. I tried using setting the DBDATE variable to Y2MD0 and using all of the the following WHERE clauses and none have worked. WHERE ORIG_DATE = year(today) || month(today) || day(today) - 1; WHERE ORIG_DATE = DATE(year(today) || month(today) || (day(today) + interval (1) day to day); WHERE ORIG_DATE = year(today) || month(today) || (day(today) + interval (1) day to day)); I'm running Informix Server 5.0. Any assistance is appreciated in advance. Thanks. Eli

Eli Glass wrote: > I need to create a query in which the where clause's field is equal > to the previous day's date formatted in YYMMDD format. I tried > using setting the DBDATE variable to Y2MD0 and using all of the the > following WHERE clauses and none have worked. > > WHERE ORIG_DATE = year(today) || month(today) || day(today) - 1; > WHERE ORIG_DATE = DATE(year(today) || month(today) || (day(today) + > interval (1) day to day); > WHERE ORIG_DATE = year(today) || month(today) || (day(today) + interval > (1) day to day)); > > I'm running Informix Server 5.0. Any assistance is appreciated in > advance. Get 5.10. What data type are you working with? If it isn't DATE, then you are making life unnecessarily difficult for yourself. Have you heard of the Y2K problem? Many of them are caused by using things like CHAR(6) in YYMMDD format to store dates. If you're using DATE types, then obviously you can use 'TODAY - 1'. If you're really using character strings, then get weaving on converting them all to DATE types. Most of your problems will relate to the fact that YEAR(TODAY) returns 1999, not 99. That leaves you with a bundle of options. I don't recall whether 5.0 has the MOD() function in it, but one possibility would be: WHERE ORIG_DATE = MOD(year(today-1), 100) || month(today-1) || day(today-1); Note that your original formulation will fail on the first of any month. I'd probably do it with an SP: -- untested code -- CREATE PROCEDURE YYMMDD(d DATE DEFAULT TODAY) RETURNING CHAR(6); DEFINE yy, mm, dd INTEGER; DEFINE rv CHAR(6); LET yy = MOD(YEAR(d)); LET mm = MONTH(d); LET dd = DAY(d); LET rv = yy || mm || dd; RETURN rv; END PROCEDURE; -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>