Got a database in Libreoffice Base using HSQLDB, in which table Expirations has columns Item and Expiry (date value). I would like to run a query that counts the number of days between ExpiryDate and the current date, outputting Item and Days Remaining expressed as whole days.
As I'm new to SQL, I'm not surprised that my first few attempts have given me syntax errors. Hopefully someone can point out where I went wrong:
SELECT DATEDIFF (DAY, CURRENT_DATE, "Expiry") AS "Days Remaining" FROM "Expirations"
I think it has something to do with first having to select all the entries in the table. Perhaps a SELECT *? If so, not sure how to link it to the DATEDIFF segment. SELECT * WHERE DATEDIFF... also throws up a syntax error.
If you're using embedded HSQLDB in LO Base then it's version 1.8. From http://www.hsqldb.org/doc/1.8/guide/guide.html:
DATEDIFF(string, datetime1, datetime2)
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.
So the argument can be either string 'dd' or 'day':
SELECT DATEDIFF ('day', CURRENT_DATE, "Expiry") AS "Days Remaining" FROM "Expirations"