List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:August 25 2005 4:50am
Subject:Re: Date arithmetic: 2005-08-31 - 1
View as plain text  
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
Thread
Date arithmetic: 2005-08-31 - 1Barbara Deaton23 Aug
  • AW: Date arithmetic: 2005-08-31 - 1Freddie Sorensen23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1SGreen23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1Michael Stassen24 Aug
RE: Date arithmetic: 2005-08-31 - 1Gordon Bruce23 Aug
RE: Date arithmetic: 2005-08-31 - 1Barbara Deaton23 Aug
  • Re: Date arithmetic: 2005-08-31 - 1Peter Brawley25 Aug
Re: Date arithmetic: 2005-08-31 - 1Michael Stassen24 Aug