From: Jonas B. Nielsen Date: March 15 1999 11:02am Subject: Date substraction problems List-Archive: http://lists.mysql.com/mysql/243 Message-Id: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII I've a database containing some records with a datefield and I need to do some operations on these dates in order to generate some reports. I have a select statement which looks something like this: select filename, count(filename) from entries where filename like '%.pdf' and date > (curdate() - 7) group by filename Which should return a report over the last week, I then vary the number to generate reports over a week, a month and two months. The dataset ranges from 1998-06-25 to 1999-03-10 When running the select statement with -30 the recordset ranges from: 1998-06-25 to 1999-03-10. when running it with: - 60 the resulting recordset is the same -7 generates a recordset ranging from 1999-03-06 to 1999-03-10 (4 days which is ok since the db hasn't been updated) -10 generates a recordset ranging from 1999-03-03 to 1999-03-10 (7 days see above) -11 generates a recordset ranging from 1999-03-02 to 1999-03-10 (8 days see above) -20 generates a recordset ranging from 1998-06-25 to 1999-03-10 (somewhere in the vicinity of 8-9 months, which also is the resulting set for -30 and -60) MySQL doesn't like when you write days after the number, I've difficulty seeing where it goes wrong except for the fact that all my reports are wrong. My MySQL installation is a rpm based 3.21.* on a RH5.2 system. I would prefer to use SQL to generate these reports, but I can always work around it in Perl... Does anybody have experience with similar problems? Jonas B. Nielsen Eml: jbn@stripped Tel: 45257449