在我的数据库中，我有表示日期的varchar值-但是我不能将它们作为日期输入到数据库中。所以，我试图从数据库中提取它们，并用它们来计算天数。
表格格式:
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); }
这段代码似乎对一些varchar有效，但是许多其他的varchar被传递为一个荒谬的值(16k days)。我相信这是因为日期被保存为12/30/1969。这表示我的日期编码有问题，但我似乎无法解决。
也许你应该在Oracle中进行计算?
select trunc(sysdate - to_date(RECEIVED, 'MM/DD/YY'))
from INTOXDM.JOINT_USE
where RECEIVED is not null;
你甚至可以这样做更新:
update INTOXDM.JOINT_USE
set DAYS = trunc(sysdate - to_date(RECEIVED, 'MM/DD/YY'))
where RECEIVED is not null;
这个故事的寓意是日期应该使用本地日期/时间格式存储在数据库中。