Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am

[Michael Stassen]

Gerard Gilliland wrote:
Michael: 

Thank you for your excellent suggestion and quick response.
You're welcome.
I now have an operable DATE_ADD.
The solution to break nPeriod into n and Period would not work directly.
That is, in the SELECT statement in the form of ...
$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
        TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) AS n,
        TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1)) AS Period,
        CASE 
          WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
          WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
        END
        AS DueDate
        FROM tblTemp";

... would fail (I think) because n and Period were calculated values.
Did you try it?  It should work.  The size of the INTERVAL, n, is allowed to 
be a calculated value, as it is in your working query below.  The problem 
you were having before is that the INTERVAL type (MONTH, YEAR, etc.) must be 
literal text (so neither a column nor calculated value), hence the need for 
the CASE statement.

However, I was sucessful using your direction and the other form of CASE
That is, I used the logic in the CASE statement directly:
$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
        CASE TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1))
          WHEN 'Month' THEN DATE_ADD(CalDate, INTERVAL 
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) MONTH)
          WHEN 'Year' THEN DATE_ADD(CalDate, INTERVAL 
TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)) YEAR)
        END
        AS DueDate
        FROM tblTemp";
This is equivalent to the first query, except it displays the value of the 
nPeriod column, rather than separate n and Period values.  DueDate will be 
the same either way.

I should point out, however, that my real suggestion was that your nPeriod 
column has 2 pieces of data, interval size and interval type, crammed into 
one column.  That's not the best way to go.  Two types of data means you 
should use two columns.

My examples below operate on your tblTemp, but I'm guessing that tblTemp is 
actually a temporary table with results from a query against your main 
tables.  If so, you'd  make the following changes in the source table.

  ALTER TABLE tblTemp
  ADD COLUMN n TINYINT,
  ADD COLUMN Period ENUM ('Day','Month', 'Year');
# Put the INTERVAL types you actually use in the ENUM
  UDATE TABLE tblTemp
  SET n = TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
      Period = TRIM(MID(nPeriod,INSTR(nPeriod,' ')+1));
  ALTER TABLE tblTemp DROP COLUMN nPeriod,
Then you could simply
  SELECT PlantName, Unit, TagName, CalDate,
        CASE
          WHEN Period = 'Month' THEN DATE_ADD(CalDate, INTERVAL n MONTH)
          WHEN Period = 'Year' THEN DATE_ADD(CalDate, INTERVAL n YEAR)
        END
        AS DueDate
  FROM tblTemp
On the other hand, if all your INTERVALs are in either months or years, then 
you can dispense with the Period column altogether, because n years = 12*n 
months:

  ALTER TABLE tblTemp ADD COLUMN n TINYINT;
  UDATE TABLE tblTemp
  SET n = TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
  WHERE nPeriod RLIKE 'Month';
  UDATE TABLE tblTemp
  SET n = 12 * TRIM(MID(nPeriod,1,INSTR(nPeriod,' ')-1)),
  WHERE nPeriod RLIKE 'Year';
# 12 months per year
  ALTER TABLE tblTemp DROP COLUMN nPeriod,
Then you could simply
  SELECT PlantName, Unit, TagName, CalDate,
         DATE_ADD(CalDate, INTERVAL n MONTH) AS DueDate
  FROM tblTemp;
Thank you,
Gerard Gilliland
[EMAIL PROTECTED]
Michael

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]