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?
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.
@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.
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.
That was great. Worked with a bit of fiddling and sql view.
Thanks
@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.