IF THEN statement would not work

•

What is DaniWeb IT Discussion Community?

You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 391,990 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,309 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.

Join our community!

Please support our MySQL advertiser:

Thread Tools Display Modes

Views: 875 | Replies: 11

Page 2 of 2

•

Join Date: Jun 2008

Posts: 36

Reputation: kvdd is an unknown quantity at this point

Rep Power: 1

Solved Threads: 2

kvdd

Offline

Light Poster

Re: IF THEN statement would not work

#11

Jul 10th, 2008

•

Originally Posted by tesuji

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/...statement.html
So I changed the query to:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
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
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

•

Originally Posted by tesuji

>> 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/5026...utput.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.

•

Originally Posted by tesuji

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

•

Join Date: Apr 2008

Posts: 295

Reputation: tesuji is an unknown quantity at this point

Rep Power: 1

Solved Threads: 41

tesuji

Offline

Posting Whiz in Training

Re: IF THEN statement would not work

#12

Jul 12th, 2008

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

Information is moving—you know, nightly news is one way, of course, but it's also moving through the blogosphere and through the Internets. I promise you I will listen to what has been said here, even though I wasn't here. Ann and I will carry out this equivocal message to the world. I'm the master of low expectations.