date_field = date_add('$date', interval 6 day);

[BiJae]

I have been building a publishing program for office summaries for the better part of the week. I written a program to calculate the first day of the week, and I want to return from the database everything that was reported during that week. I am doing this for each division, separately using a while loop, etc, so my select is a little complex:

Select * from tblsummary WHERE office = $cur_office AND set_date = date_add('$weekof', interval 6 day);

I have looked at several examples and even have an interval program working on other sites, however for the life of me I can't get this one to return the correct results. When I set it to = only I get nothing (when I know there is at least one for the week). If I set it to >= I get returns from next week and even futher out.

Can some one tell me what I am doing wrong? I've tackled much larger problems than this today and perhaps my brain is just fried. I looked at date_add on mysql.com, and I have read the manual I purchased on MySql, but I cannot see what I am doing wrong here. Any advice is greatly apprecaited.

Thank you,

Brian

 

[sleipnir214]

The >= part sounds right to me. If you only set the lower bound, you'll get everything after that date.

If you want a set of records between two values, you have to have something like:

column >= <low_value> AND column <= <high_value>


low_value and high_value in this case would be the start date and the the start_date plus 6 days, respectively.



Want the best answers? Ask the best questions:

http://www.catb.org/~esr/faqs/smart-questions.html

TANSTAAFL!!

 

[KarveR]

you should be (strangely) using date_sub...., also as this progresses, you will get everything from 6 dats ago .. if you want the last full week, then you need to be a little clever with this beast.

breaking out my laptop, the code is buried on that ..brb



______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.

 

[KarveR]

Even better, remembered I posted this here a few weeks ago:

*note, doing 2 queries, the first sets up 2 user defined variables for the following query to use (start and end dates).
These are clever, no matter what day of the week you run this you will always get the *previous* full week sun->sat inclusive. not sure if this is what you wanted tho. It decides what day you are on and works backwards regardless of the current real day of the month.

SELECT 
@startday:=if(dayofweek(curdate())=1,
@startday:=(1+6),@startday:=(dayofweek(curdate()))+6),
 @endday:=if(dayofweek(curdate())=1,@endday:=1,
@endday:=dayofweek(curdate()));

SELECT *
FROM 
tblsummary 
WHERE office = $cur_office 
AND set_date >= 'date_sub(curdate(), interval @startday day)' 
AND set_date <= (date_sub(curdate(), interval @endday day));

______________________________________________________________________
There's no present like the time, they say. - Henry's Cat.