Re: Problem with DATE_ADD

[Michael Stassen]

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
[EMAIL PROTECTED]
// 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());
 


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