Ask Your Question
0

How to make a filter for yesterday in base

asked 2018-10-09 21:55:23 +0200

PieterX gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2018-10-09 22:03:51 +0200

Ratslinger gravatar image

updated 2018-10-10 02:48:29 +0200

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.

edit flag offensive delete link more

Comments

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 gravatar imagePieterX ( 2018-10-10 08:57:22 +0200 )edit

MySQL DATEDIFF() function requires exactly 2 parameters.

See here.

BigRAl gravatar imageBigRAl ( 2018-10-10 13:34:54 +0200 )edit

@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.

Ratslinger gravatar imageRatslinger ( 2018-10-10 17:48:56 +0200 )edit

That worked! Thank you Ratslinger. I understand the importance of information about the database version

PieterX gravatar imagePieterX ( 2018-10-11 08:48:07 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-10-09 21:55:23 +0200

Seen: 62 times

Last updated: Oct 10 '18