CODEWITHSUNDEEP
mysqlsql
David542
David542

Reputation: 110093

How to set variable in mySQL

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?

Upvotes: 0

Views: 66

Answers (2)

Rahul
Rahul

Reputation: 77846

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)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

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.

Upvotes: 2

Related Questions