MySQL using IF statements

Question

I have a table with subscription values and want to calculate the time when the subscription expires.

I use the following statement:

SELECT contractType, paymentReceivedOn FROM payments WHERE id=21
AND IF (contractType = 'abo3', ADDDATE(paymentReceivedOn, 'INTERVAL 3 MONTH') AS expiryDate, 0)

My above statement works if I leave out the AS expiryDate part, however, then I cannot seem to get the result from the caculation of ADDDATE out.

How can I adjust my query so that it gives me the expiryDate based on paymentReceivedOn + 3 months?

BugFinder · Accepted Answer · 2011-06-05 18:41:01Z

1

Because its not part of the selection

    SELECT contractType, paymentReceivedOn,
IF (contractType = 'abo3', ADDDATE(paymentReceivedOn, INTERVAL 3 MONTH), NULL)  
AS expiryDate FROM payments WHERE id=21

Should probably work

(removed quotes from round the interval)

edited Jun 5, 2011 at 18:41

answered Jun 5, 2011 at 17:57

BugFinder

17.9k4 gold badges42 silver badges58 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Frank Vilea Over a year ago

Thanks BugFinder, your code works really well. What if I wanted to find abo6 which is 6 months into the future? I tried to put in another if but that did not work? Thanks again.

Frank Vilea Over a year ago

I used a very ugly way that seems to work: SELECT IF (contractType = 'abo3', ADDDATE(paymentReceivedOn, 'INTERVAL 3 MONTH'), IF (contractType = 'abo6', ADDDATE(paymentReceivedOn, 'INTERVAL 6 MONTH'), IF (contractType = 'abo12', ADDDATE(paymentReceivedOn, 'INTERVAL 12 MONTH'), NULL))) AS expiryDate FROM payments WHERE id=21

BugFinder Over a year ago

In what way didnt it work? on face value that looks like it should work.

Frank Vilea Over a year ago

Hmm.. I just tried your statement again and realized that the fault was with the statement. It gives me an expiryDate, but it is the same date as the paymentReceivedOn. Any ideas?

Frank Vilea Over a year ago

The little '' are the problem. Could you please edit your answer and I'll tick it off. Thanks again and sorry for the confusion.

|

Collectives™ on Stack Overflow

MySQL using IF statements

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related