Mysql user defined variable date type in where clause
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
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