Comparing two date values.

[mat-tastic]

Hey Guys,

 

I have never had to do this before, but someone wants me to help them.

 

I have done the rest of the coding for the system, however:

 

The client has to renew their server (TS Server) and two weeks after that the renew deadline day, they want the servers to be removed if they have not renewed in that two weeks.

 

How would I work out when two weeks has passed from the renew date?

 

e.g two weeks after 2008/06/05

[Zhadus]

What format is the date in, the automatic MySQL datestamp?

[severndigital]

i have just been working on a somewhat similar issue myself.

 

some question need to be answered first for the easiest solution.

 

1. what version of php are you using?

2. how is the date stored now?

3. how automated does this need to be?

  (i.e. is this script going to run on it's own at a given time?, or will the user manually run the script?)

 

I ask what version of PHP you have because there are inherent classes for dealing with Date and Time in 5.2.x

 

Answer those up and we can figure something out for you.!

 

-C

 

 

[mat-tastic]

It uses DATE_ADD(NOW(), INTERVAL 2 WEEK) to store the renew date, I need to work out, the date two weeks after that.

 

Thanks for your help so far.

[discomatt]

Assuming you're storing DATE_ADD(NOW(), INTERVAL 2 WEEK) into `dateColumn`

 

DATE_ADD(`dateColumn`, INTERVAL 2 WEEK)

[revraz]

Yep, do it in SQL and not PHP.

[mat-tastic]

Sorry not to sure i understand what you mean?

 

The script would be run manually.

 

It just needs to check what date it would be if it was two weeks after the renewal data in the database, then compare that to the current date.

 

if the current date is equal or past it, it kills the server.

[discomatt]

SELECT `account` FROM `table` WHERE DATE_ADD(`expires`, INTERVAL 2 WEEK) > NOW()

 

Any rows that are returned have an `expires` value older than 2 weeks.

[mat-tastic]

Thank you I will try it!

[Barand]

SELECT `account` FROM `table` WHERE DATE_ADD(`expires`, INTERVAL 2 WEEK) > NOW()

 

Any rows that are returned have an `expires` value older than 2 weeks.

 

if expires + 2 week > now() then the records are younger than 2 weeks

 

for older than 2 weeks you are looking for

 

expires < NOW() - INTERVAL 2 WEEK

[mat-tastic]

SELECT `account` FROM `table` WHERE DATE_ADD(`expires`, INTERVAL 2 WEEK) > NOW()

 

Any rows that are returned have an `expires` value older than 2 weeks.

 

if expires + 2 week > now() then the records are younger than 2 weeks

 

for older than 2 weeks you are looking for

 

expires < NOW() - INTERVAL 2 WEEK

 

Thanks to both of you, I will report back shortly.

[discomatt]

Thanks for spotting that Barand. Got my mouth facing the wrong way

[mat-tastic]

Can I just confirm it would be this:

 

SELECT `account` FROM `table` WHERE DATE_ADD(`expires`, NOW()) < INTERVAL 2 WEEK

 

[mat-tastic]

I got it working with:

 

"SELECT * FROM `servers` WHERE DATE_ADD(`renew_date`, INTERVAL 2 WEEK) < CURDATE()";

 

Can someone explain to me how that works? To me it looks like the < should be > ????

[Zhadus]

The current date is LATER, and therefore a HIGHER number than the renew date. Therefore you'd want it LESS THAN the current date by an interval of 2 weeks. Hope that makes sense.

[Barand]

I find it easier to think of it as "where renewdate <  the date 2 weeks ago"

 

i.e.

 

"SELECT * FROM `servers` WHERE `renew_date` < CURDATE() - INTERVAL 2 WEEK";