Mysql ADDDATE() or DATE_ADD() with table columns?

Question

How do I do something like the following

SELECT ADDDATE(t_invoice.last_bill_date, INTERVAL t_invoice.interval t_invoice.interval_unit) 
  FROM t_invoice

...where the column t_invoice.last_bill_date is a date, t_invoice.interval is an integer and t_invoice.interval_unit is a string.

What I like about the ADDDATE() function is that if I write a

SELECT ADDDATE('2010-01-31', INTERVAL 1 MONTH)

It conveniently returns the last day of February - 2010-02-28. So it knows that 1 MONTH means 28 days for Feb (29 on leap year), 31 if odd number month, 30 if even number month. I would like to preserve this feature in my query.

Basically, I want to determine a person's next bill date based on his desired billing frequency. Is there a better way to achieve this?

Does that query work for you? – Senseful Jun 16 '10 at 23:34 — Senseful, Jun 16 '10 at 23:34

a1ex07 · Accepted Answer · 2010-06-16T23:43:34.587

2

SELECT t_invoice.last_bill_date +  INTERVAL 
   CASE t_invoice.interval_unit 
    WHEN 'minute' THEN t_invoice.interval 
    WHEN 'hour' THEN t_invoice.interval*60
    WHEN // etc...
    END MINUTE as col1
 FROM t_invoice

OR

SELECT 
   CASE t_invoice.interval_unit 
    WHEN 'minute' THEN t_invoice.last_bill_date +  INTERVAL t_invoice.interval MINUTE
    WHEN 'hour' THEN t_invoice.last_bill_date +  INTERVAL t_invoice.interval HOUR
    WHEN // etc...
    END as col1
 FROM t_invoice

edited Jun 16 '10 at 23:43

answered Jun 16 '10 at 23:35

a1ex07

36,826
12
90
103

When I do a SELECT DATEADD('2010-01-30', INTERVAL 1 MONTH), it conveniently gives me the last day of February - 2010-02-28. Is there an easy way to modify your query to support it? – John Jun 16 '10 at 23:37
@John: You mean 1 month should be interpreted as 30 days? – Senseful Jun 16 '10 at 23:39
@eagle - 30 days if April, June etc... 31 days if Jan, March, May, July etc.. 28 days or 29 days of Feb. – John Jun 16 '10 at 23:41

Mysql ADDDATE() or DATE_ADD() with table columns?

1 Answers1