IF THEN statement would not work

[kvdd]

hello, I try the following query:

SELECT IF TIME(hour_end) > '21:00:00' THEN  timediff(time(hour_end),'21:00') AS diff ENDIF FROM `hours`

Why whould this not work?
Any help would be appreciated.

[kvdd]

Now I have this working query, but I preffer one result instead of two in the query below. And I would perform that with a IF else, but that is not working.

Here is the query:

SELECT 
	hour_id, 
	TIMEDIFF( TIME( hour_end ) , '21:00' ) AS diff1, 
	TIMEDIFF( TIME( hour_end_o ) , '21:00' ) AS diff2
FROM `hours` 
WHERE (
	TIME( hour_end ) > '21:00'
	OR 
	TIME( hour_end_o ) > '21:00'
	)
		AND (
			time( hour_end ) != '00:00:00'
			OR time( hour_end_o ) != '00:00:00'
			)

My question is now, I only want one diff as total, not two differencess(diff1 and diff2 in the query) as in the query above. How do I do that?

[kvdd]

Code optimalisation:

SELECT 
	hour_id, 
	TIMEDIFF( TIME( hour_end ) , '21:00:00' ) AS diff1,
	TIMEDIFF( TIME( hour_end_o ) , '21:00:00' ) AS diff2
FROM `hours` 
WHERE (
	TIME(hour_end) > '21:00:00'
	OR 
	TIME(hour_end_o) > '21:00:00'
	)

Daniweb where is your responsetime within 3 hours!!:icon_cheesygrin:

BTW: question is the same: How to get one diff instead of two difference (e.g. diff1 and diff2 in the query).

[varmadba]

Try This

SELECT 
HOUR_ID,
IF(TIME(hour_end) > '21:00:00',     
   TIMEDIFF(TIME( hour_end ),'21:00:00'),
   IF(TIME(hour_end_o) > '21:00:00',TIMEDIFF( TIME( hour_end_o ) , '21:00:00'),'hour_end_o LESS THAN 21:00:00 HOURS')) AS DIFF
FROM HOURS

this query will assume that both hour_end and hour_end_o wont be greater than 21:00:00

[peter_budo]

Daniweb where is your responsetime within 3 hours!!:icon_cheesygrin:

Who told you there is 3h respond time? Everybody here has his/her own life beside dani who is here 24/7 ;)

[tesuji]

SELECT IF TIME(hour_end) > '21:00:00' THEN  timediff(time(hour_end),'21:00') AS diff ENDIF FROM `hours`

What do you think of correct syntax?

SELECT IF ... THEN ... ELSE ... ENDIF   AS diff

krs,
tesu

[kvdd]

@varmadba: that did not work.

@peter_budo: you know it was a little joke :icon_wink:

@tesuji: you are right, only the AS diff must outside the IF?

This code does not work:

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	AS diff 
	ENDIF 
FROM 
	`hours`

Error: You use the wrong syntax, look at the manual .. for the right syntax to use near ')
THEN TIMEDIFF(TIME(' .. blabla

This does also not work:

SELECT 
CASE WHEN (
TIME( hour_end ) > '21:00:00'
)
THEN (
TIMEDIFF( TIME( hour_end) , '21:00:00' ) 
)
END AS diff
FROM `hours`

Error : I get only NULL results.

Anybody knows something about IF THEN statements or CASE WHEN THEN statements?

[tesuji]

hi kvdd,

if statement is correct, also your case statement seams to be correct. You can put an else part to it to assign a value instead of getting NULL if if condition evaluates false. Two reasons why you might not get the expected results:

1. no hour_end value satisfies the condition TIME( hour_end ) > '21:00:00' (possibly already checked)

2. incompatible time formats, check the result from this select statement:
select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours' .

You may choose sql strict mode to get all error messages.

krs,
tesu

[kvdd]

2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course

I have set mysql to strict mode, but I dont get a better error with this query. My goal is to get only a result back when hour_end is more then 21:00. Otherwise, I cannot calculate with 21:00.
But this query returns an error, and I think the syntax is correct :icon_confused:

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	AS diff 
	ENDIF 
FROM 
	`hours`

[tesuji]

Dear kvdd

Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.

>> 2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course

Yeah, here is more ! Did you ever see a + sign in front of the time difference?

Of course, so what about item #1, did you ever check whether time part of hour_end really satisfies condition TIME( hour_end ) > '21:00:00' ?

krs,
tesu

[kvdd]

Dear kvdd

Your IF - statement is again WRONG. Pls, look at syntax in post #6 again.

You are right, but ELSE is not needed: http://dev.mysql.com/doc/refman/5.0/en/if-statement.html
So I changed the query to:

SELECT 
	IF (TIME(hour_end) > '21:00:00') 
	THEN TIMEDIFF(TIME(hour_end),'21:00:00') 
	ENDIF <this an d .. >
	AS diff <this, have I changed, is this corrected no w?>
FROM 
	hours

So as you see, I changed it, but MySQL gives an error at line 2:
1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')
THEN TIMEDIFF(TIME(hour_end),'21:00:00')
ENDIF
AS diff
FROM
hours
L' at line 2

>> 2) returns:
2008-05-19 09:00:00
09:00:00
21:00:00
-12:00:00
.. and here more of course

Yeah, here is more ! Did you ever see a + sign in front of the time difference?

So I think I explained it wrong, so I let you see a picture what "select hour_end, TIME( hour_end ), '21:00:00', TIMEDIFF( TIME( hour_end) , '21:00:00' )
from 'hours'" returns:
http://www.turboimagehost.com/p/502634/query_output.JPG.html

As you see, (and I think that we here misunderstood each other) '-'(min) results as "- 18:00:00". I want to avoid those, so I get only true results e.g. 02:00:00 or 01:20:00 and so on. That is what I want to do with my IF THEN ENDIF statement (and there are good results, whose can return 01:00:00, but not shown in the picture).
Thus, in real language: if hour_end is above 21:00 do then the following calculation, (hour_end - 21:00). What always results above 00:00:01, because the IF THEN ENDIF statement you know. I dont want to do a timediff if it is incorrect, e.g. less then 21:00:00.

Of course, so what about item #1, did you ever check whether time part of hour_end really satisfies condition TIME( hour_end ) > '21:00:00' ?

krs,
tesu

I want to say a big thank in advance to you, for the time you already spend about my question. I am sorry if I dont know you again.
Do you, if you have time, look again about this?

Thanks

[tesuji]

hello kvdd,

there must be at least one space between END and IF in ENDIF: END IF;

If you omit the else part, you will get NULL , if condition is not satisfied.

I have checked your picture and it only shows negative values in 3rd column. Therefore the condition is never met and the select returns NULL only.

krs,
tesu