List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:September 5 2001 1:10am
Subject:Re: BETWEEN DATES FUNCTION
View as plain text  
At 5:03 PM -0700 9/4/01, Chad Berryman wrote:
>I am trying to create a MySQL SELECT statement using PHP where I am 
>getting a filtered list back by a date in the database formatted as 
>yyyy-mm-dd where the date falls between a one week window that is in 
>constant motion.
>
>For example, take todays date (as a julian calendar number) and a 
>date that is set 7 days in the future (also as a julian calendar 
>number and find all rows in the database where the date field (as a 
>julian number) falls between the julian number of the window, i.e. 
>today and one week from today.
>
>The SELECT statement that I came up with is this
>
>SELECT *,DATE_FORMAT(birthday,'%M %d'),
>DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d'))
>FROM table_name
>WHERE DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) >= $yday AND 
>DAYOFYEAR(DATE_FORMAT(birthday,'$yr-%m-%d')) <= $yday_hi";
>
>The PHP variables $yr, $yday, and $yday_hi are set as
>$yr is the current 4 digit year
>$yday is the current julian calendar date for today
>$yday_hi is the julian calendar date one week in the future.

You're making this way too hard, I suspect.  To find all birthdays from
a target date to the target date plus a week, do this:

... WHERE birthday >= $target_date
     AND birthday <= DATE_ADD($target_date,INTERVAL 7 DAY)

>
>This select statement works fine until I get to December 24th of any 
>year, and the $yday_hi goes beyond the actual number of julian days 
>in this year. So I have created an if statement that says if the 
>date in the future is bigger than the julian date for December 31 of 
>this year, then subtract the 2 and set the date in the future as the 
>difference.
>
>For example, if  there are 364 days this year, and today's julian 
>calendar date is 360, the one week in the future would be (360 + 7) 
>or 367. So subtract 364 from 367 which is 3 and set the $yday_hi 
>variable to 3.
>This makes the numbers work. So if today is December 30th (or 364) 
>the week in the future date would become Jan 6 (or 6 as a julian 
>date).
>
>It is at this time that the select statement look like
>SELECT ....... WHERE birthday >= 364 (today) AND <= 6 (one week from today)
>
>My select statement will ONLY work if $yday is smaller than $yday_hi.
>
>Is there a way to make MySQL understand that I need dates at the end 
>of the year, and at the beginning ??
>
>I hope that makes sense.
>
>Chad


-- 
Paul DuBois, paul@stripped
Thread
BETWEEN DATES FUNCTIONChad Berryman5 Sep
  • Re: BETWEEN DATES FUNCTIONPaul DuBois5 Sep
    • Comparing Datesdupon12 Sep
      • Re: Comparing DatesAttila Soki12 Sep
      • Re: Comparing DatesBill TQO Adams12 Sep