Hi, and help for a new user please.
I am creating a small database in OpenOffice 4.1.10 to record details of alarm activations for my clients. I need to be able to call up a report on demand that shows the entries only for the previous 30 days.
I have cannot find a way (if there is one) within either a Query or a Report to limit the output to only 30 days.
I am used to working with relational databases but have no knowledge of SQL coding.
Can anyone assist please.
[SOLVED] Finding records for last 30 days
[SOLVED] Finding records for last 30 days
Last edited by robleyd on Sun Jun 13, 2021 10:55 am, edited 2 times in total.
Reason: Add green tick
Reason: Add green tick
OpenOffice 4.1.10 on Windows 10
Re: Finding records for last 30 days
Code: Select all
SELECT "some", "fields" FROM "some table(s)" WHERE DATEDIF('day', "date column", CURRENT_DATE)<=30
which is documented here http://www.hsqldb.org/doc/1.8/guide/ch09.html
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding records for last 30 days
If you are using the built in HSQL database engine, you can use the DATEDIFF and CURDATE function to write a WHERE statement similar to the one in the following query.
The 'dd' in DATEDIFF causes the difference to be calculated in days. I assumed that TAble1 has a column called DateTime.
Code: Select all
SELECT * FROM "Table1" WHERE DATEDIFF('dd', "DateTime", CURDATE()) < 30
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Finding records for last 30 days
Thanks for the replies but I can't get either suggestion to work.
I am using HSQL Database Engine so could I clarify please:-
One suggestion uses DATEDIF and the other DATEDIFF
One uses CURDATE() and the other CURRENT_DATE
Are these interchangeable or are they version specific, and if so which is correct for V4.1.10?
Thanks
I am using HSQL Database Engine so could I clarify please:-
One suggestion uses DATEDIF and the other DATEDIFF
One uses CURDATE() and the other CURRENT_DATE
Are these interchangeable or are they version specific, and if so which is correct for V4.1.10?
Thanks
OpenOffice 4.1.10 on Windows 10
Re: Finding records for last 30 days
Either CURDATE() or CURRENT_DATE will work. I think the use of DATEDIF() is a typo.
I tested my query in BASE and it works. Are you sure the column containing your dates contains numeric dates and not text?
I tested my query in BASE and it works. Are you sure the column containing your dates contains numeric dates and not text?
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Finding records for last 30 days
You find all expressions (except DATEDIF) on the documentation page I have linked.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: Finding records for last 30 days
Sorted. Thanks for all the help.
OpenOffice 4.1.10 on Windows 10