asked on

Using different variables for INTERVAL 'types' in DATE_ADD statement

SETUP:
Using mySQL 4.1
ref DATE_ADD: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

I have a table that has an item_id, hist_date, item_frequency_qty, and item_frequency_unit columns

+---------+--------------+-----------------------+------------------------+
| item_id | hist_date | item_frequency_qty | item_frequency_unit |
+---------+--------------+-----------------------+------------------------+
| 00001 | 2005-04-13 | 7 | DAY |
| 00002 | 2005-04-13 | 2 | MONTH |
| 00003 | 2005-04-13 | 6 | MONTH |
| 00004 | 2005-04-13 | 42 | DAY |
| 00005 | 2005-04-13 | 1 | YEAR |
+---------+--------------+-----------------------+------------------------+

I want to create a a query that will add the amount of days/months/years from the table to 'hist_date'.

However, it seems that that 'INTERVAL' statement does not compute the 'item_frequency_unit' correctly.
Using SELECT DATE_ADD(hist_date, INTERVAL '7' DAY) works,
even SELECT DATE_ADD(hist_date, INTERVAL `item_frequency_qty` DAY) will work,
but using SELECT DATE_ADD(hist_date, INTERVAL `item_frequency_qty` `item_frequency_unit`) gives me an error.

It seems it does not like the `item_frequency_unit` variable as a 'type' for the INTERVAL statement.

Anyone know a way around this problem?

ASKER CERTIFIED SOLUTION

nagki

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

akshah123

What if you try concat function to first concatenate the string?

so try,

SELECT DATE_ADD(hist_date,concat( 'INTERVAL ', `item_frequency_qty`,' ', `item_frequency_unit`) )

NeonDevil

ASKER

Nagki, your statement worked:
CASE WHEN item_frequency_unit='DAY' THEN DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QTY DAY)
WHEN item_frequency_unit='MONTH' THEN DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QTY MONTH)
ELSE DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QTY YEAR)

akshah123, I've tried concatenating the string when I was troubleshooting before, and that didn't work. I even inserted your statement in case I had my syntax wrong. It doesn't work with my version of mySQL (4.1).