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:[EMAIL PROTECTED]]
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:[EMAIL PROTECTED]]
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
|
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
