Search code examples
sqleclipsefirebirdfirebird2.5

Firebird SELECT DATEDIFF query returns nothing

I'm using Eclipse's "Database Development" perspective to run queries against a Firebird database. One particular field, studydatetime, is giving me problems.It's a timestamp field holding the date and time of a medical exam. I have a text file that holds only the date, and I need to match the two to fill in some blanks in the text file. But when I run something like

select datediff(day,1,'2017-07-26')  
from worklistdisplay  
rows 1;

I get nothing - not even a query with no rows. Even when I run something like

select datediff(day,'2017-07-01','2017-07-26')
from rdb$database;

I get the same result. The "Status" tab in the Eclipse results window says 

(0 rows affected)
Elapsed Time:  0 hr, 0 min, 0 sec, 25 ms.

But no results are returned.

I'm using the "Generic JDBC_1.x" connection type in Firebird, with driver file "jaybird-full-3.0.1.jar". The DB version is 2.5.4.

Solution

  • cast to date will help 

     select datediff(day,cast('2017-07-01' as date),cast('2017-07-26' as date)) from rdb$database

    this return 25