help with a date issue

[aron]

Hi all.

I have in my database a datefield (called date) its also a DATE.

it prints the date that the entry was added to the database

2006-09-10
2006-08-17
2006-09-01
2006-08-29

its all a subscription thing. ( the records have a yearly subscription, before an email is sent off to them asking them to renew)

so what i need to do is work out when the subscription expires ( 12 months time) so

start 2006-09-10 exp 2007-09-10
start 2006-08-17 exp 2007-08-17
start 2006-09-01 exp 2007-09-01
start 2006-08-29 exp 2007-08-29

i then need to work out how many months are left before the subscription runs out.

so

start 2006-09-10 exp 2007-09-10 12 months left
start 2006-08-17 exp 2007-08-17 11 months left
start 2006-09-01 exp 2007-09-01 12 months left
start 2006-05-29 exp 2007-05-29 8 months left

cheers aron.

[Weedpacket]

Depending on which DBMS you're using, it's bound to have a variety of functions for working with its date/time types. Have you tried looking at them?

[aron]

Dbms ???

[Piranha]

dbms

[aron]

I dont understand this DBMS. I use MySQL. and all i need to do is

request the date from the database

$date_original= $line_card_details["date"];

then i need to add 1 year to the date

$date_end = $line_card_details["date"] + 1 year;

then i need to see how many months from todays date to the end date.

cheers aron.

[aron]

im trying to work this out myself

//Date string
$d1 = $line_card_details["date"];
$old_date = date('d-m-Y',$d1);
$date = $old_date;
//Get day,mount,year use parsing by '-'
$data=explode("-",$date);
//Create stamp, with old day and mounth and today year
$stamp=mktime(0,0,0,$date[1],$date[0],date("Y"));
//And after that you can format date by this data
$date=date('d-m-Y',$stamp);
print $date;

d1 prints like this "2006-09-10"
old_date prints like this "31-12-2005"

its ment to print like this "2007-09-10"

so what am i doing wrong.

[Installer]

You can do it all with a query:

$query = "SELECT date, DATE_ADD(date, INTERVAL 12 MONTH) AS exp_date, 
          PERIOD_DIFF(DATE_FORMAT(DATE_ADD(date, INTERVAL 12 MONTH), '%Y%m'), DATE_FORMAT(NOW(), '%Y%m')) AS diff  

          FROM my_table";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result)) {
    echo 'start ' . $row['date'] . ' exp ' . $row['exp_date'] . ' ' . $row['diff'] . ' months left<br />';
}

The second line can probably be made more concise, but the query should work as is.

[aron]

thanks installer that worked perfectly.

cheers aron.