CODEWITHSUNDEEP
mysqldate
parrker9
parrker9

Reputation: 958

Treating string as MySQL statement?

I have a table that holds the information of installation dates for shop displays in a store. Every shop display has a certain warranty period, which can be 6 months, 1 year, 2 years etc., which I store in another table as "6 MONTH", "1 YEAR", "2 YEAR", etc.

Is it possible to calculate the expiry dates for each shop display using a single MySQL query? I am looking for something like this:

SELECT t1.install_date, (t1.install_date + INTERVAL t2.period) as expiry_date FROM t1, t2

So, I am basically trying to treat string values that I get from the t2 table as a part of MySQL statement. Is this possible to do? For my query MySQL does not give any errors but displays the values in the expiry_date column like this: "20110228", "20110224" for the corresponding values of t1.install_date "2011-02-28", "2011-02-24".

Upvotes: 2

Views: 146

Answers (2)

Jesse Cohen
Jesse Cohen

Reputation: 4040

This is harder than I thought because DATE_ADD won't accept a string as an INTERVAL unit. So the solution I came up with was to dynamically convert all your YEAR values to months by multiplying by 12.

Also, you need to specify a join condition, otherwise you will calculating intervals for the entire cartesian product of the two tables.

SELECT t1.install_date, DATE_ADD(t1.install_date, 
                                 INTERVAL IF(SUBSTRING_INDEX(t2.period,' ','-1')='YEAR',          
                                             SUBSTRING_INDEX(t2.period,' ','1')*12, 
                                             SUBSTRING_INDEX(t2.period,' ','1')) 
                                          MONTH) as expiry_date
FROM t1,t2 WHERE t1.id = t2.id

Upvotes: 1

krtek
krtek

Reputation: 26607

Have a look at STR_TO_DATE(str,format). But I think this will be really hard. Good luck.

Upvotes: 1

Related Questions