Query to find data for previous week 2

[gradinumcp]

Hi there...

I am trying to get a query that can find if a Loandate falls in last week...so basically check if loandate is between sunday or monday of last to friday or saturday of lastweek...

Any clues:

Select * from loan where last weeks sunday<Loandate>last week's saturday.

Thanks

 

[KarveR]

I start by setting a couple of variables for start and end days, then run the select using these:
SELECT @startday:=if(dayofweek(curdate())=1,@startday:=(1+6),@startday:=(dayofweek(curdate()))+6),@start:= date_sub(curdate(), interval @startday day);

SELECT @endday:=if(dayofweek(curdate())=1,@endday:=1,@endday:=dayofweek(curdate())),@end:=date_sub(curdate(), interval @endday day);

SELECT *
FROM loan
WHERE
loandate >= @start
AND loandate <= @end;





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

 

[feherke]

Hi

KarveR's solution is the nice and fast one, but for small amount of data I prefer a shorter way :

[b]select[/b] * [b]from[/b] loan [b]where[/b] date_format(loandate,[i]'%X %U'[/i])=date_format(current_date()-interval 7 day,[i]'%X %U'[/i]);

Feherke.

http://rootshell.be/~feherke/

 

[KarveR]

See now, when i asked that ages ago, no-one could answer it so I ended up with my (no rather clunky looking) solution.

I always figured thare had to be a more sophisticated method but never found it.

A star awarded to you sir!.

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