In my database, I have varchar values that signify dates - however I cannot input them into the database as dates. So, I am trying to pull them from the database and use them to calculate days.
Format in table:
mm/dd/yy
My SQL Statement (in PHP):
$conn = oci_connect("user", "pass", "(description=(address=(protocol=tcp)(host=host)(port=1533))(connect_data=(service_name=sid)))");
$sel = "select RECEIVED from INTOXDM.JOINT_USE where RECEIVED is not null";
$par = oci_parse($conn, $sel);
$exe = oci_execute($par);
$fetch = oci_fetch_all($par,$array);
echo $fetch;
$arraynum = 0;
while ($array) {
$arraynum = $arraynum + 1;
$date = $array[RECEIVED][$arraynum];
$today=date("m/d/y");
$now=strtotime($today); // or your date as well
$your_date=strtotime("$date");
$DAYS=($now - $your_date)/(60*60*24);
$upd = "update INTOXDM.JOINT_USE set DAYS = '$DAYS' where RECEIVED = '$date'";
$pars = oci_parse($conn, $upd);
$exe = oci_execute($pars); }
This code seems to work for some of the varchars, however many others are passed as a ridiculous value (16k days). This is because the date is being saved as 12/30/1969, I believe. This signifies that I have a problem with my date coding, but I cannot seem to figure it out.
Perhaps you should do the calculation in Oracle?
select trunc(sysdate - to_date(RECEIVED, 'MM/DD/YY'))
from INTOXDM.JOINT_USE
where RECEIVED is not null;
You can even do the update this way as well:
update INTOXDM.JOINT_USE
set DAYS = trunc(sysdate - to_date(RECEIVED, 'MM/DD/YY'))
where RECEIVED is not null;
The moral of the story is that dates should be stored in databases using the native date/time formats.