I need a Date!

Hello Folks, I need to delete records for a table that have dates that are older than one month from the today's date. The querry I cooked up is as below. Am I right. Please help. "SELECT a.applic_id, a.status, l.event, FROM application a, audit_log l WHERE a.applic_id BETWEEN 1 AND 10000 bla, bla, bla... AND l.log_time <= CURRENT - 1 UNITS MONTH <---This's the chap giving me a head ache. Thanks a ton. sherwin

Sherwin Jaleel wrote: > > Hello Folks, > I need to delete records for a table that have dates that are older > than one month from the today's date. The querry I cooked up is as > below. Am I right. Please help. > "SELECT a.applic_id, > a.status, > l.event, > FROM application a, > audit_log l > WHERE a.applic_id BETWEEN 1 AND 10000 > bla, bla, bla... > AND l.log_time <= CURRENT - 1 UNITS MONTH <---This's the chap > giving me a head ache. The subtract 1 UNITS MONTH operation is undefined when l.log_time is the 31st of a month where the preceding month does not have 31 days: March, May, July, October, December It is also undefined on the 29th or 30th of a month where the preceding month does not have 29 or 30 days: March (excluding leap years for the 29th) You have to decide what you want the date to be when CURRENT is the 29th, 30th or 31st of a month. You can then write code to implement your decision -- probably a stored procedure. Or you can decide that all months are 30, or 31, or any other convenient number of days and simply subtract 30 UNITS DAY, which will always work about from during January 0001 :) -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>