Last 20 days with MySQL

software development

#1

Crap, I can’t get this right. I’m giving up and asking for help. How can I get the most recent twenty days out of my MySQL table? My last try was:

Select CheckDate, SpeciesStage, Source, Count From tblLATpassage
where CheckDate > (curdate() - interval 20 day)
order by CheckDate

but I get no lines. CheckDate is formated like 2/8/2007

This signature line intentionally blank.


#2

try

Select CheckDate, SpeciesStage, Source, Count From tblLATpassage
where TO_DAYS(CheckDate) > (TO_DAYS(curdate()) - interval 20 day)

$50 off and 3 free domains with code: [color=#CC0000]DH3[/color] Sign Up NOW or More Codes Here


#3

Thanks for suggesting To_Days.

One of my problems was the table hasn’t been updated as frequently as I thought so there were no records less than 20 days from current day. When I changed to 30 days, then I started getting some more understandable results.

I’m going with
Select CheckDate, SpeciesStage, Source, Count From tblLATpassage
where TO_DAYS(CheckDate) > (TO_DAYS(curdate()) - 30)

Although when I go back 30 days, my original query works as well:
Select CheckDate, SpeciesStage, Source, Count From tblLATpassage
where CheckDate > (curdate() - interval 30 day)

Hey, I just discovered datediff. With this function, I can write the query as:
Select CheckDate, SpeciesStage, Source, Count From tblLATpassage
where datediff(curdate(),CheckDate) < 30
order by CheckDate

This signature line intentionally blank.