List: General Discussion « Previous MessageNext Message » From: Gerard Gilliland Date: July 17 2004 6:19pm Subject: Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am View as plain text
```Gerard Gilliland replied:

Michael:

Thank you for your excellent suggestion and quick response.
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.
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";

Thank you,
Gerard Gilliland
gerardg@stripped

Michael Stassen wrote:

With DATE_ADD(date,INTERVAL expr type), date and expr can be dynamic, but
type must be literally one of the types in the list.  It cannot come from a
column or function.  It would be nice if it could, but it cannot.

One solution would be to break nperiod into n, an int, and period, a
char(x).  Then you could

SELECT ...
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 ...

A simpler option, if you can express all your intervals as the same type,
would be to simply replace nperiod with n.  Given your example, one could
replace "1 year" with "12 month".  That is, put 12 in for n in the 1 year
case, then run the query to always add months:

SELECT ...
DATE_ADD(CalDate, INTERVAL n MONTH) AS DueDate
FROM ...

Michael

Gerard Gilliland wrote:

> I need some help with a DATE_ADD problem.
> I can't seem to run a query with DATE_ADD(date, INTERVAL expr type)
> where expr and type are dynamic.
> It works fine with date as dynamic.
> I am attempting to add 1 year to 2004-04-15 for a DueDate of 2005-04-15
> and add 3 months to 2004-03-10 for a DueDate of 2004-06-10.
>
> Field Names:
> PlantName, Unit, TagName, CalDate, nPeriod
>
> Sample Data from Records:
> Valmont, 5, FCV96008, 2004-04-15, 1 Year
> Valmont, 5, FT0701, 2004-03-10, 3 Month
>
> This Works: (but it adds 1 year to both records)
> \$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
>         DATE_ADD(CalDate, INTERVAL 1 Year) AS DueDate FROM tblTemp";
> Note: "1 Year" is static.
>
> This Works: (but it adds 3 months to both records)
> \$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
>         DATE_ADD(CalDate, INTERVAL 3 Month) AS DueDate FROM tblTemp";
> Note: "3 Month" is static.
>
> This Fails:
> \$qry = "SELECT PlantName, Unit, TagName, CalDate, nPeriod,
>         DATE_ADD(CalDate, INTERVAL nPeriod) AS DueDate FROM tblTemp";
> Note: nPeriod is dynamic and contains '1 Year' with FCV96008
>  and '3 Month' with FT0701
> Warning message:
> Warning: mysql_num_fields(): supplied argument is not a valid MySQL result resource
>
> I have also split nPeriod in Pexpr and Ptype using the MID() function
> and placed them appropriately but that also fails.
> I have changed the data to all caps (YEAR and MONTH) but it also fails.
>
> General server information: July 15, 2004
> Operating system: Linux
> Kernel version: 2.4.26
> Apache version: 1.3.31 (Unix)
> PERL version: 5.8.0
> Path to PERL: /usr/bin/perl
> Path to sendmail: /usr/sbin/sendmail
> PHP version: 4.3.7
> MySQL version: 4.0.18-standard
>
> I would appreciate any help.
> Thank you,
> Gerard Gilliland
> gerardg@stripped
>
>
> // Table definition:
>   \$tblName = "tblTemp";
>
>   \$tblDef = "PlantName VARCHAR(50), ";
>   \$tblDef .= "Unit VARCHAR(10), ";
>   \$tblDef .= "TagName VARCHAR(255), ";
>   \$tblDef .= "CalDate DATE, ";
>   \$tblDef .= "nPeriod VARCHAR(50) ";
>
>   if(!mysql_query("CREATE TABLE \$tblName (\$tblDef)"))
>     die ('Cannot Create Table \$tblName ' . mysql_error());
>
> // Table Data:
>   if(!mysql_query("INSERT INTO \$tblName VALUES(
>     'Valmont', '5', 'FCV96008', '2004-04-15', '1 Year')"))
>     die ('Cannot Insert into \$tblName ' . mysql_error());
>   if(!mysql_query("INSERT INTO \$tblName VALUES(
>     'Valmont', '5', 'FT0701', '2004-03-10', '3 Month')"))
>     die ('Cannot Insert into \$tblName ' . mysql_error());
>
>