CODEWITHSUNDEEP
mysqlsqldatabase
Kris.Kodira
Kris.Kodira

Reputation: 43

How to use variables from select inside where clause

I'm getting the halfway-point between two dates into a variable and I want to check if the halfway-point is today. I can select what I need without a problem but once I put the variables into the WHERE clause it seems to not work.

I tried outputting both dates I need and they are the same but when I compare them in the WHERE clause I don't get any results.

This shows me the columns and middle_date and todays_date are the same in one column

SELECT @order_date := order_date, @delivery_date := delivery_date,
ROUND(UNIX_TIMESTAMP(ADDDATE(@order_date, ((DATEDIFF(@delivery_date, @order_date)/2)))),0) AS middle_date, 
UNIX_TIMESTAMP(DATE(NOW())) AS todays_date FROM `order`

enter image description here

Now when I do this I don't get any results:

SELECT @order_date := order_date, @delivery_date := delivery_date,
ROUND(UNIX_TIMESTAMP(ADDDATE(@order_date, ((DATEDIFF(@delivery_date, @order_date)/2)))),0) AS middle_date, 
UNIX_TIMESTAMP(DATE(NOW())) AS todays_date FROM `order`
WHERE ROUND(UNIX_TIMESTAMP(ADDDATE(@order_date, ((DATEDIFF(@delivery_date, @order_date)/2)))),0) = UNIX_TIMESTAMP(DATE(NOW()))

enter image description here

I expect the query to return the row where the todays_date and middle_date are same.

Upvotes: 0

Views: 58

Answers (1)

Joakim Danielson
Joakim Danielson

Reputation: 52088

You can do it without variables and without unix_timestamp

SELECT order_date, delivery_date,
   ADDDATE(order_date, ((DATEDIFF(delivery_date, order_date)/2))) AS middle_date, 
   DATE(NOW()) AS todays_date 
FROM `order`
WHERE ADDDATE(order_date, ((DATEDIFF(delivery_date, order_date)/2))) = DATE(NOW())

Upvotes: 1

Related Questions