From: Pintér Tibor
Date: June 24 2007 5:59am
Subject: Re: Selecting rows by DATE ranges
List-Archive: http://lists.mysql.com/mysql/207696
Message-Id: <467E0857.5090509@tibyke.hu>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

date(), date_format()

t

Miguel Cardenas írta:
> Hello list
> 
> I found a little problem with an application am developing, in particular 
> creating reports by DATE ranges.
> 
> Examples:
> 
> select ... where date>"2007-01-01";
> returns all records where date is greater (and equal inclusive) to 2007-01-01
> 
> select ... where date>="2007-01-01";
> returns all records where date is greater/equal to 2007-01-01
> 
> the ">" and ">=" have the same effect
> 
> select ... where date>"2007-01-01" and date<"2007-01-20";
> returns all records where date is greater/equal to 2007-01-01 and less 
> than "2007-01-20"
> *** DOES NOT RETURN RECORDS FROM DAY *20*
> 
> select ... where date>"2007-01-01" and date<="2007-01-20";
> returns all records where date is greater/equal to 2007-01-01 and less 
> than "2007-01-20" although I'm using "<="
> *** DOES NOT RETURN RECORDS FROM DAY *20*
> 
> My doubts are:
> 
> 1. how can I retrieve rows with a date>"..." NOT INCLUDING the day of the 
> specified date, I mean apply a strict GREATHER THAN
> 
> 2. how can I retrieve rows with date<="..." INCLUDING the day of the specified 
> date. currently I have to do a date>="date1" and date<="date2+1day"
> 
> I need to retrieve rows in this way
> 
> date>X
> date>=X
> date<X
> date<=X
> date>X and date<Y
> date>=X and date<Y
> date>=X and date<=Y
> date>X and date<=Y
> 
> and so... didn't find a function to specify ranges of dates and the LESS 
> THAN/EQUAL operator does not include the last day, so my reports with "<=" 
> are done by adding one day but don't like to use it this way since it could 
> be confusing and generate errors on reports.
> 
> Thanks for any comment,
> Miguel
>