I have the following statement:
SELECT
start_date,
end_date,
DATE(now()) BETWEEN start_date AND end_date should_be_live
FROM
mytable
WHERE
DATE(now()) BETWEEN start_date AND end_date should_be_live = 1
Is there a way to set a variable should_be_live = the expression DATE(now()) BETWEEN start_date AND end_date. If so, how would I do this?
If by "variable" you mean "column in the result set", then yes. You are basically doing it:
SELECT start_date, end_date,
( DATE(now()) BETWEEN start_date AND end_date) as should_be_live
FROM mytable;
If by "variable", you mean that you want to define a column in the select and then filter on it, you cannot do that with where (unless you use a subquery). You can use having:
SELECT start_date, end_date,
( DATE(now()) BETWEEN start_date AND end_date) as should_be_live
FROM mytable
HAVING should_be_live = 1;
This use of having is a MySQL extension.
with statement in mysql?
No unfortunately, you will have to repeat the same expression DATE(now()) BETWEEN start_date AND end_date in your WHERE clause unless you are trying to access it in a outer query like
SELECT * FROM (
SELECT
start_date,
end_date,
DATE(now()) BETWEEN start_date AND end_date as should_be_live
FROM mytable ) xxx
WHERE should_be_live = 1;
Column alias are accessible only to GROUP BY, HAVING and ORDER BY clause and not to WHERE clause (that to it's a MySQL extension and not standard SQL)
