Using DATE_ADD with a column name as an interval value

Question

I have a table containing products, start date and interval value:

product_name                    start_date              expiry_period
Domain Registration (1 Year)    2013-12-08 00:00:00     1 Year
Domain Registration (1 Year)    2013-12-01 00:00:00     1 Year
Website Hosting (Bronze)        2013-12-19 00:00:00     1 Year
Website Hosting (Silver)        2013-12-20 00:00:00     1 Year
Website Hosting (Silver)        2013-12-21 00:00:00     1 Year
Domain Registration (2 years)   2014-01-04 00:00:00     2 Year
Domain Registration (1 Year)    2014-01-04 00:00:00     1 Year
Website Hosting (Silver)        2014-01-06 00:00:00     1 Year
Domain Registration (2 years)   2014-01-06 00:00:00     2 Year
Domain Registration (1 Year)    2014-01-07 00:00:00     1 Year
Domain Registration (1 Year)    2014-01-10 00:00:00     1 Year
Website Hosting (Bronze)        2014-01-12 00:00:00     1 Year

I am trying to add a calculated value to my select statement to add an interval to start_date so that my dataset will return with start and end dates programmatically.

Here is what I have at the moment:

select 
    product_name, 
    start_date,
    expiry_period
    DATE_ADD(start_date, INTERVAL expiry_period) as end_date
from
    tbl_products

However, it returns an error with respect to the DATE_ADD string (invalid SQL syntax).

All the SO articles I read seem to indicate that the expression and type should be separate (i.e. DATE_ADD(start_date, INTERVAL expiry_value expiry_type)). Of course, this is not so, and can I just go through the period in one field?

, , ? SUBSTRING_INDEX , .

Answer 1

- 1 Year . INTERVAL - MySQL , .

, , :

SELECT 
    product_name, 
    start_date,
    expiry_period,
    @num:=CAST(expiry_period AS UNSIGNED),
    @p  :=SUBSTR(expiry_period, CHAR_LENGTH(@num)+2),
    CASE
      WHEN @p='Year' THEN DATE_ADD(start_date, INTERVAL @num YEAR)
      WHEN @p='Month' THEN DATE_ADD(start_date, INTERVAL @num MONTH)
      WHEN @p='Day' THEN DATE_ADD(start_date, INTERVAL @num DAY)
      WHEN @p='Week' THEN DATE_ADD(start_date, INTERVAL @num WEEK)
    END AS end_date
FROM
    tbl_products

- , , ( CAST , , ). CASE clause

- (, ) - ( , 1 = 7 , e tc)

Answer 2

:

select product_name, start_date, expiry_period,
       (case when expiry_period like '%day'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 DAY) as end_date
             when expiry_period like '%week'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 WEEK) as end_date
             when expiry_period like '%month'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 MONTH) as end_date
             when expiry_period like '%year'
             then DATE_ADD(start_date, INTERVAL expiry_period + 0 YEAR) as end_date
       end)
from tbl_products;

(+ 0 * 7) .