Less than or equal to with dates

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Less than or equal to with dates

Post by icesolid »

I am trying to run a query to find out if there are any due dates that are seven days from now or older.

Here is what I have so far. Not producing a result, however I do have dates in the system that are seven days from today and older (seven days from today being where I am starting from and search any dates that are less than todays date.

Code: Select all

<?php
$over7 = date("m-d", strtotime("+7 days"))
 
mysql_query("SELECT `control_number` FROM `cases` WHERE `sent_to_customer`=false AND `due_date`>='$over7'");
?>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Less than or equal to with dates

Post by Christopher »

I am a little confused by "seven days from now or older" and "search any dates that are less than todays date" ??? Your logic will find any dates 7 or more days in the future.

I am also not sure what "`sent_to_customer`=false" will produce. What type of field is sent_to_customer?
(#10850)
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Re: Less than or equal to with dates

Post by aaronhall »

Double check the `sent_to_customer` column. MySQL doesn't have a boolean column type, and the conditional for that column is only going to evaluate to true if the column is a VARCHAR, CHAR or SET type. Even then, 'false' is a string literal and should be enclosed in quotes. Booleans are usually implemented using TINYINT with values of 0 or 1.
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Less than or equal to with dates

Post by icesolid »

OK. I am trying to find all "cases" that have a "due_date" that is older than 2008-03-19. (2008-03-18, 2008-03-17, 2008-03-16 and so on NOT 2008-03-20, 2008-03-21, 2008-03-22).

The field "due_date" uses the standard DATE field type, with the format of 0000-00-00.

Dont worry about the sent_to_customer field. I remvoed that.
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Re: Less than or equal to with dates

Post by aaronhall »

You're looking for rows where due_date is less than $over7
icesolid
Forum Regular
Posts: 502
Joined: Mon May 06, 2002 9:36 pm
Location: Buffalo, NY

Re: Less than or equal to with dates

Post by icesolid »

Yes
User avatar
flying_circus
Forum Regular
Posts: 732
Joined: Wed Mar 05, 2008 10:23 pm
Location: Sunriver, OR

Re: Less than or equal to with dates

Post by flying_circus »

You have a couple of different options, and your best bet is to use the MySQL date functions.

I have a similar system, and I find it easiest to work with unix timestamps. If it were up to me, this is how I would do it:

Code: Select all

<?php
  $over7 = strtotime("+7 days");
 
  mysql_query("SELECT control_number FROM cases WHERE sent_to_customer = false AND UNIX_TIMESTAMP(due_date) >= '$over7'");
?>
Now, you may notice some quirks, depending on how important it is that you return results within 7 days from this very second. Since your db only stores your date in the 0000-00-00 format, I believe when the unix timestamp is created, it sets the time as 00:00:00 (12 am). If you need a finer resolution, you can modify your $over7 variable to something like this:

Code: Select all

<?php
  $sTimeInMorning = date('Y-m-d');
  $over7 = strtotime($sTimeInMorning);
?>
User avatar
aaronhall
DevNet Resident
Posts: 1040
Joined: Tue Aug 13, 2002 5:10 pm
Location: Back in Phoenix, missing the microbrews
Contact:

Re: Less than or equal to with dates

Post by aaronhall »

icesolid wrote:Yes
Indeed :wink:
Post Reply