[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1094
  • Last Modified:

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?





0
NeonDevil
Asked:
NeonDevil
1 Solution
 
nagkiCommented:
this will work:

 SELECT CASE WHEN item_frequency_unit='DAY' THEN DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QUTY DAY)
      WHEN item_frequency_unit='MONTH' THEN DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QUTY MONTH)
            ELSE DATE_ADD(HIST_DATE,INTERVAL ITEM_FREQUENCY_QUTY YEAR) END FROM INTERVALS;
0
 
akshah123Commented:
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`) )
0
 
NeonDevilAuthor Commented:
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).

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now