Ask Your Question
0

Base: query for table entries made 3 days ago

asked 2018-05-10 00:00:17 +0200

chrismlewis gravatar image

updated 2018-05-10 02:51:39 +0200

LibreTraining gravatar image

I have a table with records. One of the fields is a date (autopopulated on the date the entry was made). I want to run a query on the table matching a date. The date to match should be '3 days ago'. I think I want to match something like ='CURRENT_DATE - 3' or = DATEADD("d",-3,CURRENT_DATE). These both yield a "format dd-mm-yyyy" type error.

To be clear, the query is looking for date matches that are 3 days prior to the date the query is run.
How would I do that?

edit retag flag offensive close merge delete

Comments

It would be extremely helpful if you would specify which DB you are using (default is HSQL embedded). This makes a difference in SQL used.

Ratslinger gravatar imageRatslinger ( 2018-05-10 00:29:00 +0200 )edit

@LibreTraining Please stop editing questions unless they actually need it. If you start an edit & don't change anything (multiple times today already) DON'T save it - CANCEL it. You are sending out needless notifications. Also, if posted as a wiki you will have wiped out the originator of the question.

Ratslinger gravatar imageRatslinger ( 2018-05-10 03:39:51 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2018-05-10 01:53:08 +0200

Ratslinger gravatar image

updated 2018-05-10 03:32:29 +0200

Hello,

There are two ways to go. First you can always use a parameter to input the date. However, it would seem if 3 days is always the case then there is another solution.

HSQL embedded (Base default) is very restrictive when dealing with SQL and even more so with date math. It is old and is in the process of being replaced by Firebird. The best thing to use with this (and works on newer HSQL databases) is DATEDIFF:

Select DATEDIFF('dd', "YOUR_FIELD", CURDATE()) FROM "YOUR_TABLE WHERE DATEDIFF('dd', "YOUR_FIELD", CURDATE()) = 3

Now my comment mentioned what DB you are using. This same statement in MySQL is:

Select DATEDIFF(CURDATE(), "YOUR_FIELD" ) FROM "YOUR_TABLE" WHERE DATEDIFF(CURDATE(), "YOUR_FIELD") = 3

Of course these samples only select the number of days different between the dates. You would of course select other fields but the WHERE statement is consistent.

If this answers your question please tick the ✔ (upper left area of answer). It helps others to know there was an accepted answer.

edit flag offensive delete link more
0

answered 2018-05-10 16:39:20 +0200

chrismlewis gravatar image

That was great. Worked with a bit of fiddling and sql view.
Thanks

edit flag offensive delete link more

Comments

@chrismlewis In future please do not respond with an answer unless it is to answer your own question. Rather use a comment for the answer you are responding to.

Also, again, If this answers your question please tick the ✔ (upper left area of answer to question). It helps others to know there was an accepted answer.

Ratslinger gravatar imageRatslinger ( 2018-05-10 18:08:43 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2018-05-10 00:00:17 +0200

Seen: 77 times

Last updated: May 10 '18