Hi, I needed to accomplish a simple task with Derby: computing an interval of days (like datedif in excel). I used the TIMESTAMPDIFF function, it seemed to work well, until I noticed a strange behaviour : sometimes the computation is OK, sometimes not. After having searched the trouble, I've found that from TIMESTAMPDIFF's point of view, the 28 march 2010 is not a different day than the 29 march of 2010. You can easily check this with this script : -- creating testing environment CREATE TABLE dummy (beginning DATE, ending DATE); INSERT INTO dummy (beginning, ending) VALUES ('2010-04-01', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-31', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-30', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-29', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-28', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-27', '2010-04-01'); INSERT INTO dummy (beginning, ending) VALUES ('2010-03-26', '2010-04-01'); -- -- checking the odd behaviour SELECT beginning, ending, {fn timestampdiff(SQL_TSI_DAY, beginning, ending )} AS "interval in days" FROM dummy; -- -- cleaning DROP TABLE dummy You'll get this result : beginning ending interval in days 2010-04-01; 2010-04-01; 0 2010-03-31 ; 2010-04-01; 1 2010-03-30 ; 2010-04-01; 2 2010-03-29 ; 2010-04-01; 3 2010-03-28 ; 2010-04-01; 3 2010-03-27 ; 2010-04-01; 4 2010-03-26 ; 2010-04-01; 5 Someone has any insight on this ? Is there a nicer way to get the interval in days that won't get into this trouble ? -- View this message in context: http://old.nabble.com/problem-with-timestampdiff-tp30997315p30997315.html Sent from the Apache Derby Users mailing list archive at Nabble.com.