Page 1 of 1

Less than or equal to with dates

Posted: Wed Mar 12, 2008 2:35 pm
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'");
?>

Re: Less than or equal to with dates

Posted: Wed Mar 12, 2008 2:42 pm
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?

Re: Less than or equal to with dates

Posted: Wed Mar 12, 2008 2:47 pm
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.

Re: Less than or equal to with dates

Posted: Wed Mar 12, 2008 2:48 pm
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.

Re: Less than or equal to with dates

Posted: Thu Mar 13, 2008 2:05 am
by aaronhall
You're looking for rows where due_date is less than $over7

Re: Less than or equal to with dates

Posted: Thu Mar 13, 2008 8:29 am
by icesolid
Yes

Re: Less than or equal to with dates

Posted: Thu Mar 13, 2008 1:35 pm
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);
?>

Re: Less than or equal to with dates

Posted: Fri Mar 14, 2008 1:29 am
by aaronhall
icesolid wrote:Yes
Indeed :wink: