I have a table which contains products, a start date and an 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'm trying to add a calculated value in my select statement to add the interval to the start_date so that my dataset returns with the start and end date programmatically.
Here's 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's returning an error against the DATE_ADD line (incorrect SQL Syntax).
All of the SO articles that I've read seem to indicate that the expression and the type need to be separate (i.e. DATE_ADD(start_date, INTERVAL expiry_value expiry_type)
) - Surely this isn't the case and I can just pass a period in one single field?
If not and since I can't change the data schema, what would be the recommended way of doing it? I contemplated using SUBSTRING_INDEX
to split the column by but this doesn't appear to work either.
It won't be easy o get your result - since you're storing it as plain 1 Year
or similar. It is not allowed to use it dynamically in INTERVAL
construct - MySQL syntax demands that you'll point both interval quantity and type.
However, there's kind of trick to resolve the matter:
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
-as you can see, this query relies on fact, that quantity always goes first (so CAST
will extract exactly it, therefore, it can be used to get interval length after this). But in any case, you'll have to recount all possible interval types in CASE
clause
Another good idea would be - to store your period in unified form (for example, always in days) - so you'll store only one number for each row (thus, 1 week=7days, e t.c.)