List:General Discussion« Previous MessageNext Message »
From:Barbara Deaton Date:August 23 2005 7:36pm
Subject:Date arithmetic: 2005-08-31 - 1
View as plain text  
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
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