I think I have it, subject to more testing. I used 3 bindings and its the content of the 3rd one that makes it work. It should not contain only the number of days, but the full interval i.e ‘+27 days’.
StartString = “2017-11-27”
EndString = “2017-11-27”
DateInterval = “+27 days”
DisplayDataPS.Bind(0, StartString)
DisplayDataPS.Bind(1, EndString)
DisplayDataPS.Bind(2, DateInterval)
It means I can change it to supply the single date and bind it twice which is cleaner and less error prone, as suggested by Dave.
StartDateString = “2017-11-27”
DateInterval = “+27 days”
DisplayDataPS.Bind(0, StartDateString)
DisplayDataPS.Bind(1, StartDateString)
DisplayDataPS.Bind(2, DateInterval)
The preparedstatement code at the insertion points :
WHERE TransactionDate BETWEEN ? AND DATE(?,?)
I think Jared had the right idea with using 3 bindings.
I was attempting to enclose in single quotes (Also tried backticks) to match the SQLite Manager code that works :
SELECT * FROM Transactions WHERE TransactionDate BETWEEN '2017-11-27' AND date('2017-11-27', '+27 Days')
Enclosing in these quotes and backticks, and putting the ‘+’ and ‘days’ in the statement rather than the binding values is the cause of the failure.
This corrected approach has 2 advantages :
I get more flexibility in respect of the DateInterval. You can use the usual range of days, weeks, months, years etc, with the usual mathematical operators. I would guess its the same when using SQLDateTime format too.
My code is much cleaner and simpler because I don’t have to declare the dates and perform the dateinterval calculation in code prior to passing the values for binding. I like the fact I can now hand off the calculation part to the database itself.
I’ve certainly learned something new today.
Thanks all for your input