Barbara,
/> ... I don't know if I'm being asked to add or subtract days.../
Why would you want to know that? ADDDATE() doesn't care:
SET @x = -1;
SELECT ADDDATE('1975-1-1', INTERVAL @x DAY);
+--------------------------------------+
| ADDDATE('1975-1-1', INTERVAL @x DAY) |
+--------------------------------------+
| 1974-12-31 |
+--------------------------------------+
PB
-----
In ADDDATE( date_value, INTERVAL expr DAYS), 'expr' can resolve to a
positive or negative int.
PB
Barbara Deaton wrote:
>Unfortunately no, because I don't know if I'm being asked to add or subtract days.
> I'm just given a value, and have to transform that into something that can be added or
> subtracted.
>
>So for example, all I get with is value 1 meaning 1 day and I need to do something
> with a date, for db2 through some calculations I turn this into 00000001. and my resulting
> SQL statement then becomes:
>
>select count(*) from cwdd where col2 - 00000001. = {d '2005-06-07'}
>
>And yes, the period is required for DB2.
>
>I'm trying to figure out what calculation or modifications I need to do to the value
> passed in, in this case 1. So that I can turn it into something I can pass down for MySQL
> to do the math on.
>
>Thanks for the suggestion though, it just won't work in this case.
>
>-Barb.
>
>-----Original Message-----
>From: Freddie Sorensen [mailto:freddie@stripped]
>Sent: Tuesday, August 23, 2005 3:45 PM
>To: Barbara Deaton; mysql@stripped
>Subject: AW: Date arithmetic: 2005-08-31 - 1
>
>Barbara,
>
>Can't you use the ADDDATE function ?
>
>http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
>
>Freddie
>
>
>
>>-----Ursprüngliche Nachricht-----
>>Von: Barbara Deaton [mailto:Barbara.Deaton@stripped]
>>Gesendet: Dienstag, 23. August 2005 21:37
>>An: mysql@stripped
>>Betreff: Date arithmetic: 2005-08-31 - 1
>>
>>All,
>>
>>I know MySQL comes with all sorts of wonderful functions to do date
>>arithmetic, the problem is the context that my application is being
>>called in I don't know if a user wants me to add or subtract days.
>>I'm just given the number of days that need to be either added or
>>subtracted from the date given.
>>
>>So for example, if your table was
>>
>>mysql> select * from dtinterval;
>>+------------
>>| datecol
>>+------------
>>2005-09-01
>>2005-08-30
>>2005-08-31
>>+--------------
>>
>>a user could enter:
>>
>>select count(*) from dtinterval where datecol - 1 = '30AUG2005'd;
>>
>>Which is our applications SQL, my part of the product is only give the
>>value 1, I have to transform that into something MySQL will understand
>>as 1 day and then pass that back into the SQL statement to be passed
>>down to the MySQL database. I transform our applications SQL into
>>select COUNT(*) from `dtinterval` where (`dtinterval`.`datecol` - 1) =
>>'1974-12-04'
>>
>>I know that just doing the -1 is wrong, since "select '2005-08-31' - 1
>>and that just gives me a year
>>
>>mysql> select '2005-08-31' - 1;
>>+------------------+
>>| '2005-08-31' - 1 |
>>+------------------+
>>| 2004 |
>>+------------------+
>>
>>What do I need to translate the 1 into in order to get back the value
>>'2005-08-30' ?
>>
>>Thanks for your help.
>>Barbara
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:
>>http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>>
>
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.10.15/80 - Release Date: 8/23/2005
