Re: How to get a difference between two dates?

[Satheesh Bandaram]

Derby 10.1 supports datetime arithmetic... Adding a constant interval to a datetime (timestampadd) or subtracting one datetime with another (timestampdiff). However, these operators are only present as JDBC escape functions, so you need to invoke them using standard JDBC function invocation syntax. ( {fn func()} Check out the (confusing) syntax in 10.1 reference manual: http://db.apache.org/derby/docs/10.1/ref/refderby.pdf ij> select {fn timestampdiff(SQL_TSI_DAY, {d '2000-01-01'}, {d '2000-01-10'})} as date_diff from oneRow; DATE_DIFF ----------- 9 1 row selected Satheesh Maciej Bliziński wrote: Hello everybody, I have a question about getting a date difference in Derby. I tried Derby online docs, Google (web and newsgroups) and got no results. For example, in PostgreSQL I can get a difference of two dates: SELECT '2000-01-10'::DATE - '2000-01-01'::DATE AS date_diff; date_diff ----------- 9 (1 row) Similar thing with Derby brings an error message: ij> SELECT DATE('2000-01-10') - DATE('2000-01-01') AS date_diff FROM dict_country; ERROR 42Y95: The '-' operator with a left operand type of 'DATE' and a right operand type of 'DATE' is not supported. ("FROM dict_country" was added only because Derby needs a FROM clause, while PostgreSQL doesn't) So, if substraction of two dates is not supported, how can I get a difference in days between two dates?