Mysql user defined variable date type in where clause

Asked Nov 08 '18 at 12:57

Active Nov 08 '18 at 13:17

Viewed 204 times

I tried to simplify my long queries with user defined variables in Mysql. Unfortunately, these variables don't behave in where clause as expected - especially variables where is stored date. Here are 3 simplified queries:

SELECT id,
       @var_date := `date`
FROM   aroma_exchanges
WHERE  @var_date >= "2000-01-01"

This returns 0 rows

Another one:

SELECT id,
       @var_date := `date`
FROM   aroma_exchanges
WHERE  Str_to_date(@var_date, "%y-%m-%d") >= "2000-01-01"

This return 0 rows as well.

SELECT id, 
       @var_date := `date` 
FROM   aroma_exchanges 
WHERE `date` >= "2000-01-01"

This one works as expected, returns a lot of rows. In my case the definition of date is very complex and in my real query I used it few times so I don't want to repeat the same subquery 5-10 times if I can store it into one variable and use it as I need to. Question: how to make the first or second query to work properly? Thanks.

edited Nov 08 '18 at 13:17

asked Nov 08 '18 at 12:57

mesnicka

2,458
8
23
31

The `select` clause gets evaluated **after** the `where` clause. So at that time the variable is not set yet. – juergen d Nov 08 '18 at 12:59
It does not make any sense to store the column value in a variable and then use it in where condition. Are you looking for aliases ? – Madhur Bhaiya Nov 08 '18 at 13:06
@MadhurBhaiya: Aliases won't work either in this case. – juergen d Nov 08 '18 at 13:13

Mysql user defined variable date type in where clause

0 Answers0