Mysql user defined variable date type in where clause
Multi tool use
up vote
0
down vote
favorite
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.
mysql date variables user-defined
asked Nov 8 at 12:57
mesnicka
80861227
add a comment |
up vote
0
down vote
favorite
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.
mysql date variables user-defined
asked Nov 8 at 12:57
mesnicka
80861227
Theselectclause gets evaluated after thewhereclause. So at that time the variable is not set yet.
– juergen d
Nov 8 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 8 at 13:06
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
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.
mysql date variables user-defined
asked Nov 8 at 12:57
mesnicka
80861227
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.
mysql date variables user-defined
mysql date variables user-defined
asked Nov 8 at 12:57
mesnicka
80861227
asked Nov 8 at 12:57
mesnicka
80861227
edited Nov 8 at 13:17
asked Nov 8 at 12:57
mesnicka
80861227
asked Nov 8 at 12:57
mesnicka
80861227
asked Nov 8 at 12:57
mesnicka
80861227
80861227
Theselectclause gets evaluated after thewhereclause. So at that time the variable is not set yet.
– juergen d
Nov 8 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 8 at 13:06
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13
add a comment |
Theselectclause gets evaluated after thewhereclause. So at that time the variable is not set yet.
– juergen d
Nov 8 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 8 at 13:06
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13
The
select clause gets evaluated after the where clause. So at that time the variable is not set yet.– juergen d
Nov 8 at 12:59
The
select clause gets evaluated after the where clause. So at that time the variable is not set yet.– juergen d
Nov 8 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 8 at 13:06
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 8 at 13:06
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53208228%2fmysql-user-defined-variable-date-type-in-where-clause%23new-answer', 'question_page');
);
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
QmjBZ5MAzJ4TCTbtM6mQ5 Zb,teVeONE8qOH7sWW3EuzRL,t 0HRs7L,XZQF g BEwN,nhlUIkdAHW2vwimkIW L,2a,UpQtJiw8YLA,Kl
The
selectclause gets evaluated after thewhereclause. So at that time the variable is not set yet.– juergen d
Nov 8 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 8 at 13:06
@MadhurBhaiya: Aliases won't work either in this case.
– juergen d
Nov 8 at 13:13