I can filter:
= CURRENT_DATE (shows only entries on this date)
Buth I want the entries of yesterday and today. TODAY(-1) isn’t possible…
I can filter:
= CURRENT_DATE (shows only entries on this date)
Buth I want the entries of yesterday and today. TODAY(-1) isn’t possible…
Hello,
Unfortunately your question doesn’t mention which database you are using in Base. This can be a variety of databases such as HSQLDB embedded(default), Firebird (expermental default), MySQL, PostgreSQL, Oracle and others.
Most likely it is one of the default databases.
For HSQLDB embedded v1.8 this:
Select * FROM YOUR_TABLE WHERE DATEDIFF('dd', YOUR_FIELD, CURDATE()) < 2 and DATEDIFF('dd', YOUR_FIELD, CURDATE()) > -1
will get dates of today and yesterday.
For Firebird embedded this:
Select * from YOUR_TABLE where YOUR_FIELD = CURRENT_DATE OR YOUR_FIELD = CURRENT_DATE - 1
Other databases may vary.
Edit: Corrected HSQLDB statement to not include future dates.
Also, to use as filter in form, just use the portion after the ‘where’ in the statements.
I use a MySQL 5.7 database with a (ODBC) connection.
I made a query as you described:
SELECT `Machine`, `Datum en tijd` FROM `logboekh5r`.`machine geschiedenis` WHERE DATEDIFF( 'dd', `Datum en tijd`, CURDATE( ) ) < 2 AND DATEDIFF( 'dd', `Datum en tijd`, CURDATE( ) ) > - 1
Buth I get a fault:
[MySQL][ODBC 8.0(w) Driver][mysqld-5.7.18-log]Incorrect parameter count in the call to native function ‘DATEDIFF’
@PieterX As stated in my answer:
Other databases may vary.
Here is the SQL used on MySQL:
Select * FROM YOUR_TABLE WHERE DATEDIFF(CURDATE(), YOUR_FIELD) < 2 AND DATEDIFF(CURDATE(), YOUR_FIELD) > -1
Hopefully this will help you understand that when asking a question it is necessary to properly include pertinent information.
That worked! Thank you Ratslinger.
I understand the importance of information about the database version