[SOLVED] Finding records for last 30 days

Discuss the database features
Post Reply
Madcapdj
Posts: 3
Joined: Fri Jun 11, 2021 12:38 pm

[SOLVED] Finding records for last 30 days

Post by Madcapdj »

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.
Last edited by robleyd on Sun Jun 13, 2021 10:55 am, edited 2 times in total.
Reason: Add green tick
OpenOffice 4.1.10 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding records for last 30 days

Post by Villeroy »

Code: Select all

SELECT "some", "fields" FROM "some table(s)" WHERE DATEDIF('day', "date column", CURRENT_DATE)<=30
assuming that "small database in OpenOffice" means this
Image
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
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Finding records for last 30 days

Post by FJCC »

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.

Code: Select all

SELECT * FROM "Table1" WHERE DATEDIFF('dd', "DateTime", CURDATE()) < 30
The 'dd' in DATEDIFF causes the difference to be calculated in days. I assumed that TAble1 has a column called DateTime.
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.
Madcapdj
Posts: 3
Joined: Fri Jun 11, 2021 12:38 pm

Re: Finding records for last 30 days

Post by Madcapdj »

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
OpenOffice 4.1.10 on Windows 10
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Finding records for last 30 days

Post by FJCC »

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?
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.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Finding records for last 30 days

Post by Villeroy »

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
Madcapdj
Posts: 3
Joined: Fri Jun 11, 2021 12:38 pm

Re: Finding records for last 30 days

Post by Madcapdj »

Sorted. Thanks for all the help.
OpenOffice 4.1.10 on Windows 10
Post Reply