DATE_ADD 1

[andyb2005]

Hi
here's my query:

"SELECT * from customer WHERE DATE_ADD(date_entered INTERVAL 14 DAY)";

date_entered is a DATE held in my database and is datetime.
I am trying to highlight the records where the date_entered has now exceeded 14 days since it was entered into the database.

I know I have got the syntax all wrong so anyone got any ideas.

 

[sleipnir214]

Entering the search string date_add into the search gadet that appears in the upper right-hand corner of every page at dev.mysql.com helped me to quickly find my way to an possible appropriate page in the MySQL online manual

That page lists date_add as:

DATE_ADD(date,INTERVAL expr type)


At first glance and without knowing the exact SQL error generated, I would hazard you're missing a comma between the two parameters of the function.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[andyb2005]

Hi
typo - I have got a comma there but it just seems to bring back all of my records instead of the ones that are 14 days and older.

 

[sleipnir214]

SELECT queries return a row whenever the WHERE clause evalues to a non-FALSE value. Your WHERE clause reads:

WHERE DATE_ADD(date_entered, INTERVAL 14 DAY)

and the statement DATE_ADD(date_entered, INTERVAL 14 DAY) will nearly always evaluate to a non-FALSE value.



I recommend that you try comparing the value generated by DATE_ADD(date_entered, INTERVAL 14 DAY) to the current time and date:

WHERE DATE_ADD(date_entered, INTERVAL 14 DAY) >= now()



Want the best answers? Ask the best questions! TANSTAAFL!

 

[andyb2005]

Hi
ok I am doing this now

"SELECT * from customer WHERE DATE_ADD(date_entered, INTERVAL 21 DAY) >=  now()
AND two_wk_follow_up = 'No'";

There are 3 records that should satisfy this query -however, it returns 3 unique results and then repeats them continously.

I'm baffled!

 

[sleipnir214]

Repeats them continuously"?

Please post your script.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[andyb2005]

Hi

All I am trying to excute is this:

$queryCheck2week = "SELECT * from customer WHERE DATE_ADD(date_entered, INTERVAL 21 DAY) >=  now()
AND two_wk_follow_up = 'No'";

$result10 = mysql_query($queryCheck2week) or die (mysql_error()); 

  while($row10 = mysql_fetch_array($result10))
  { 
  echo $row10[two_wk_follow_up] . " " . $row10[date_entered] . "<bR>";
  }

It returns the results in groups of 3 for 35 instances.

 

[andyb2005]

I have isolated the code from my main script and it returns the correct records.

It would seem that I must have a problem elsewhere which I should be able to find. Thanks for your help!

 

[andyb2005]

I have it more or less working now but how do I just display those records that are 14 days and older and not records less than 14 days old?

 

[andyb2005]

It's OK - I've figured it out!