I have a query that has a user-defined variable set on top of the main query. Its something like this.
set @from_date = '2019-10-01', @end_date = '2019-12-31';
SELECT * FROM myTable
WHERE create_time between @from_date AND @end_date;
It works just fine when I executed it in MySQL Workbench, but when I put it to Excel via MySQL ODBC it always shows an error like this.
I need that user-defined variable to works in Excel. What am I supposed to change in my query?
The ODBC connector is most likely communicating with MySQL via statements or prepared statements, one statement at a time, and user variables are not supported. A prepared statement would be one way you could bind your date literals. Another option here, given your example, would be to just inline the date literals:
SELECT *
FROM myTable
WHERE create_time >= '2019-10-01' AND create_time < '2020-01-01';
Side note: I expressed the check on the create_time, which seems to want to include the final quarter of 2019, using an inequality. The reason for this is that if create_time be a timestamp/datetime, then using BETWEEN with 31-December on the RHS would only include that day at midnight, at no time after it.
Use subquery for variables values init:
SELECT *
FROM myTable,
( SELECT @from_date := '2019-10-01', @end_date := '2019-12-31' ) init_vars
WHERE create_time between @from_date AND @end_date;
Pay attention:
SELECT is used, not SET;:= is used, = operator will be treated as comparing one in this case giving wrong result;init_vars) may be any, but it is compulsory.Variable is initialized once but may be used a lot of times.
If your query is complex (nested) the variables must be initialized in the most inner subquery. Or in the first CTE if DBMS version knows about CTEs. If the query is extremely complex, and you cannot determine what subquery is "the most inner", then look for execution plan for what table is scanned firstly, and use its subquery for variables init (but remember that execution plan may be altered in any moment).
