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());
>  
> 

Thread
Problem with DATE_ADD - Gerard Gilliland, July 16 2004 5:16am
Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48am 

© 1995-2004 MySQL AB. All rights reserved.
MySQL.com Home Site Map Contact Us Privacy Policy Trademark Info 

Thread
Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amGerard Gilliland17 Jul
  • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amMichael Stassen19 Jul
  • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amGerard Gilliland20 Jul
    • Re: Problem with DATE_ADD - Michael Stassen, July 16 2004 7:48amMichael Stassen20 Jul