1

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is executed always, even when it shouldn't. What I am doing wrong?

UPDATE BILLS
SET STATE = 2,
DATE_REACT =
CASE 
WHEN (DATE_EM <= CURTIME() <= ADDTIME(DATE_EM, '00:02:00')) THEN ADDTIME(CURTIME(), '00:13:00')
ELSE ADDTIME(DATE_REACT, '00:13:00')
END
WHERE STATE = 1;
  • What is the datatype of date_em? Is it TIME, DATE, DATETIME or TIMSETAMP? – ypercubeᵀᴹ Sep 26 '14 at 10:05
  • The datatype is TIME – william Sep 26 '14 at 10:09
  • What exactly are you trying to achieve? – Mureinik Sep 26 '14 at 10:09
  • OK then. Because the naming worried me that you might be comparing dates to times. – ypercubeᵀᴹ Sep 26 '14 at 10:33
4

You can't combine boolean expressions like that. Try it this way:

UPDATE BILLS
SET STATE = 2,
DATE_REACT =
CASE 
WHEN (DATE_EM <= CURTIME() AND CURTIME() <= ADDTIME(DATE_EM, '00:02:00')) THEN ADDTIME(CURTIME(), '00:13:00')
ELSE ADDTIME(DATE_REACT, '00:13:00')
END
WHERE STATE = 1;
1

The problem is this part:

( DATE_EM <= CURTIME() <= ADDTIME(DATE_EM, '00:02:00') )

This is not allowed in SQL. The syntax (a <= b <= c) is valid in some languages, like Python but not in SQL. MySQL allows it but as you have found out, does something different and not what you want.

Replace it with (a <= b AND b <= c):

( DATE_EM <= CURTIME() AND CURTIME() <= ADDTIME(DATE_EM, '00:02:00') )

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.