CODEWITHSUNDEEP
phpmysqlsql
Alex
Alex

Reputation: 99

DATEDIFF calculation between specific days without years

I am trying to calculate interest being paid every quarter from a purchased date.
Can DATEDIFF calculate without inputting the years and have it continuously calculating the days every year between the 2 dates?

eg: DATEDIFF current date without year and mature date without year

DATEDIFF ('10-25','*-01-01') while * is a wild card of years. Can this be done or is there a ways of doing it?

CURRENT CODE:

// Escapes for security
$pdate = mysqli_real_escape_string($con, $_POST['pdate']);
$mdate = mysqli_real_escape_string($con, $_POST['mdate']);
$amt = mysqli_real_escape_string($con, $_POST['amt']);
$first = mysqli_real_escape_string($con, $_POST['first']);
$last = mysqli_real_escape_string($con, $_POST['last']);
$pid = mysqli_real_escape_string($con, $_POST['pid']);
$cno = mysqli_real_escape_string($con, $_POST['cno']);
$madd = mysqli_real_escape_string($con, $_POST['madd']);
$bene = mysqli_real_escape_string($con, $_POST['bene']);
$swc = mysqli_real_escape_string($con, $_POST['swc']);
$bacc = mysqli_real_escape_string($con, $_POST['bacc']);
$bank = mysqli_real_escape_string($con, $_POST['bank']);
$badd = mysqli_real_escape_string($con, $_POST['badd']);
$bno = mysqli_real_escape_string($con, $_POST['bno']);

// Query with DATEDIFF calculation
$sql="INSERT INTO contacts (
  nodays, q1, q, pdate, mdate, amt, first, last,
  pid, cno, madd, bene, swc, bacc, bank, badd, bno
) 
VALUES (
  DATEDIFF('$mdate','$pdate'),
  (DATEDIFF('$mdate','$pdate') * '$amt' / 365 * 0.025),
  '$pdate','$mdate','$amt','$first','$last',
  '$pid','$cno','$madd','$bene','$swc','$bacc','$bank','$badd','$bno'
)";

// Executing and error checking of query
if (!mysqli_query($con,$sql)) {
  die('Error: ' . mysqli_error($con));
}

echo "Client record has been added to the database!";

// Close MySQL
mysqli_close($con);
?>

Upvotes: 0

Views: 112

Answers (1)

Brian DeMilia
Brian DeMilia

Reputation: 13248

FYI, this isn't quite working (as you can see in the sql fiddle). But it's a start. I'll leave it up in case it helps anyone else and/or you toward getting something that works.

Assuming you have a table purchases like -

create table purchases (purchase_date date);
insert into purchases values ('2014-10-25');

Create a table named generator with n rows, n being the number of years into the future you'll ever want a query to go (in this example, 5):

 create table generator (n int);
 insert into generator values (1),(2),(3),(4),(5);

Then you can run the following to get the interest dates at that interval for 5 years ahead of the purchase date, with the difference in days between the interest date and purchase date on each row:

select purchase_date,
       concat(@rw := @rw + 1, '-01-01') as interest_date,
       datediff(purchase_date, concat(@rw := @rw + 1, '-01-01')) as date_diff
  from purchases
 cross join (select @rw := 2014) r
 cross join generator
 where n <= 5
union all
select purchase_date,
       concat(@rx := @rx + 1, '-04-01') as interest_date,
       datediff(purchase_date, concat(@rx := @rx + 1, '-04-01')) as date_diff
  from purchases
 cross join (select @rx := 2014) r
 cross join generator
 where n <= 5
union all
select purchase_date,
       concat(@ry := @ry + 1, '-10-01') as interest_date,
       datediff(purchase_date, concat(@ry := @ry + 1, '-10-01')) as date_diff
  from purchases
 cross join (select @ry := 2014) r
 cross join generator
 where n <= 5
order by 2

See fiddle: http://sqlfiddle.com/#!2/6d367/3/0

Upvotes: 2

Related Questions