How to use variable in where clause in mysql?

Question

0.00/5 (No votes)

See more:

I want to take customized date and time value in a variable then i want to use it in where clause as a condition.

For that what i did is, using 2 select statments and inserting into 2 variables respectivly.its storing in that vaiable successfully.

when i use those variable in where clause(sql query) its not giving output and if i hardcode those vaiable values output will come.

Please help me to overcome this issue.The way i used this variable is correct in where clauese or how to use it in correct way

What I have tried:

select concat(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' ','00:00:00') into @fromdate;
       select concat(DATE_SUB(CURDATE(), INTERVAL 1 DAY),' ','24:59:59') into @todate;


SELECT 
*
	FROM
cust_item_mst gsk
   JOIN
u_stockiest_item gsk_mapping ON gsk_mapping.c_stockiest_code = gsk.c_c2code
   AND gsk_mapping.c_stockiest_item_code = gsk.c_code
   LEFT JOIN
u_stockiest_item AS stk_ucode ON stk_ucode.c_ucode = gsk_mapping.c_ucode
   LEFT JOIN
cust_inv_det dist_inv ON dist_inv.c_c2code = stk_ucode.c_stockiest_code
   AND stk_ucode.c_stockiest_item_code = dist_inv.c_item_code
   JOIN
cust_inv_mst inv_mst ON inv_mst.c_c2code = dist_inv.c_c2code
   AND inv_mst.c_br_code = dist_inv.c_br_code
   AND inv_mst.c_year = dist_inv.c_year
   AND inv_mst.c_prefix = dist_inv.c_prefix
   AND inv_mst.n_srno = dist_inv.n_srno
   LEFT JOIN
cust_act_mst retail_mst ON retail_mst.c_c2code = inv_mst.c_c2code
   AND retail_mst.c_code = inv_mst.c_cust_code
   JOIN
cust_inv_sub_det invrcvd ON invrcvd.c_c2code = inv_mst.c_c2code
   AND invrcvd.c_br_code = inv_mst.c_br_code
   AND invrcvd.c_year = inv_mst.c_year
   AND invrcvd.c_prefix = inv_mst.c_prefix
   AND invrcvd.n_srno = inv_mst.n_srno
   join
gsk_csv_stkdata gsk_stk on gsk_stk.lcc2code=stk_ucode.c_stockiest_code    
WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= @fromdate
   AND invrcvd.d_ldate <= @todate
order by gsk_stk.lcc2code

Posted 29-Aug-19 19:40pm

Member 11337367

Updated 30-Aug-19 1:06am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2019-08-29T19:54:00

Solution 1

It should be:

SQL

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= @fromdate
   AND invrcvd.d_ldate <= @todate

As you can see, i removed unnecessary signs: '+ +' around the variables.

For further details, please see: MySQL Variables[^]

[EDIT]

Quote:
first i tried without + sign still it did not come

So, try to set value to the variable different way:

SQL

SET  @fromdate:='2019-08-30 00:00:00'
--or
SELECT @fromdate:=MAX(dateTimeField)
FROM yourtable;

Posted 29-Aug-19 19:54pm

Maciej Los

Updated 29-Aug-19 20:24pm

v3

Comments

Member 11337367 30-Aug-19 2:06am

first i tried without + sign still it did not come

Maciej Los 30-Aug-19 2:24am

Please, see updated answer.

Member 11337367 30-Aug-19 2:31am

both are not giving output..
WHERE
gsk.c_c2code = '00S204'
AND gsk_stk.lcc2code = '003004'
AND invrcvd.d_ldate >= '2019-08-29 00:00:00'
AND invrcvd.d_ldate <= '2019-08-29 24:59:59'
order by gsk_stk.lcc2code for this way its giving result

Maciej Los 30-Aug-19 3:08am

As i mentioned, you have to set your variable this way:

SET  @fromdate:='2019-08-29 00:00:00';
SET  @todate:='2019-08-29 23:59:59';

Member 11337367 30-Aug-19 3:28am

I did like that only but its not effective

Maciej Los 30-Aug-19 3:52am

So, try to cast:

SET  @fromdate:=CAST('2019-08-29 00:00:00' AS DATETIME);
SET  @todate:=CAST('2019-08-29 23:59:59' AS DATETIME);

Member 11337367 30-Aug-19 5:08am

no use means its not working.i feel that its not validating the variable

Maciej Los 30-Aug-19 5:09am

???

OriginalGriff · Answer 2 · 2019-08-29T19:55:00

Look at your code:

SQL

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= '+@fromdate+'
   AND invrcvd.d_ldate <= '+@todate+'
order by gsk_stk.lcc2code

You aren't using the variable content - you have enclosed them in a string, so the string is what the comparison is made with.
Try:

SQL

WHERE
gsk.c_c2code = '00S204'
   AND gsk_stk.lcc2code = '003004'
   AND invrcvd.d_ldate >= @fromdate
   AND invrcvd.d_ldate <= @todate
order by gsk_stk.lcc2code