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@parawebic.com] 
Sent: Tuesday, August 23, 2005 3:45 PM
To: Barbara Deaton; mysql@lists.mysql.com
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@sas.com]
Gesendet: Dienstag, 23. August 2005 21:37
An: mysql@lists.mysql.com
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=freddie@parawebic.com


    



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=barbara.deaton@sas.com