-1

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?

John
  • 32,403
  • 80
  • 251
  • 422

1 Answers1

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
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