Much to my surprise, LO Base can’t pass the DATE_SUB and DATE_ADD query unless you choose to “Run SQL Query Directly.” The problem with this is that a) you can no longer modify the query in Design View, and b) user input prompts no longer work (:prompt type items).
So, I came up with a messy workaround, which works using the SUBDATE function. It seems the problem with DATE_SUB( ‘2014-08-01’, INTERVAL 1 MONTH) is the “INTERVAL 1 MONTH” part. SUBDATE allows entering a number of days to subtract or the INTERVAL statement. Use the INTERVAL statement and it breaks, days = no problem.
Here’s my example to for a BETWEEN command to return the last day of last month and for the begin date, the first day 3 months ago:
DATE_FORMAT(SUBDATE( DATE_FORMAT( MAKEDATE( :Yr, ( :Mon ) * 28 ), '%Y-%m-01' ),30*3), '%Y-%m-01') AS `Begin Date`, DATE_FORMAT(Last_Day(SUBDATE( DATE_FORMAT( MAKEDATE( :Yr, ( :Mon ) * 28 ), '%Y-%m-01' ),30)), '%Y-%m-%d') AS `End Date`,
The above prompts the user to enter the year (:Yr, can be 14 or 2014) and the month (:Mon, enter number). 30 seemed like a safe number to subtract. It basically gets the rough date of the correct month, which is then reformatted to be the 1st of the month and end of the month.
For my use, this is part of a multiple query, meaning if I want to run the report for last month (September 2014 as of this writing), another query is using the :Yr and :Mon to create a BETWEEN statement that looks like:
BETWEEN {D '2014-09-01' } AND {D '2014-09-30' }
Then the second query, using the “hacked” subdate function, is returning results between 2014-06-01 and 2014-08-31.
Just to recap:
- Query 1 prompts user for the month and year they want to run the report.
- Query 1 takes that, turns it into a begin and end date for a BETWEEN statement and outputs that to something like `Current_Month_Column`
- Query 2 automatically grabs the same input, pipes the input to create a begin date and and end date of the month prior to the month returned for Query 1.
Hopefully that will help others running into the same issue.