Ask Your Question

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

asked 2015-12-24 11:55:56 +0100

TheDatabaseGuy gravatar image

updated 2020-08-07 02:58:59 +0100

Alex Kemp gravatar image

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" 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?

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2020-08-07 02:59:09.561175


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!!

TheDatabaseGuy gravatar imageTheDatabaseGuy ( 2015-12-24 15:57:20 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2015-12-30 18:24:08 +0100

doug gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-12-24 11:55:56 +0100

Seen: 30,857 times

Last updated: Dec 30 '15