How to calculate the numbers of days between two dates in a query in base?

[TheDatabaseGuy]

How can I calculate the numbers of days between two dates in a query in base?

Simple example: In a query I have a field called DateArrived and another called DatePurchase. Both display fine.

I added a field called NOW() and it correct showed the current date on each record.

I then added a field called:

DateArrived DatePurchase

(…and Base converted it to:)

"DateArrived" - "DatePurchase"

…in order to calculate the number of days between the two dates. I got the error:

SQL Status: 37000
Error code: -16
Wrong data type: java.lang.NumberFormatException in statement [SELECT “DatePurchase” “DatePurchase”, “DateArrived” “DateArrived”, “DateArrived” - “DatePurchase” “Days” FROM “T_Drives”]

How can I resolve this please?

[TheDatabaseGuy]

I’ve tried:
DAYS(DateArrived; DateArrived)
But this produces “Invalid expression”

I’ve trying:
DAYS(DateArrived, DateArrived)
But this produces "Access is denied: DAYS in statement [SELECT “DatePurchase” “DatePurchase”, “DateArrived” “DateArrived”, DAYS( “DateArrived”, “DateArrived” ) “DayTwo” FROM “T_Drives”]

Pulling my hair out!!

[doug]

It appears you are looking for the DATEDIFF function in HSQLDB v1.8. The documentation is available at this link, scroll down to the right place.

The function is summarized in the documentation as follows:

DATEDIFF(string, datetime1, datetime)

    returns the count of units of time elapsed from datetime1 to datetime2. 
    The string indicates the unit of time and can have the following values 'ms'='millisecond', 
    'ss'='second','mi'='minute','hh'='hour', 'dd'='day', 'mm'='month', 'yy' = 'year'. 
    Both the long and short form of the strings can be used.

Thus, in your case, the function you need probably would look something like the following:

SELECT DATEDIFF('dd', "DateArrived", "DatePurchase")
FROM "TableName"

The limitations of date functions in HSQLDB v 1.8 do cause headaches, for example see this question and answer, but this specific issue here seems quite solvable.