duanbenzan4050
2014-07-15 14:19
浏览 30

为什么这个SQL语句不会使用字符串(mm / dd / yy)并将其转换为天?

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.

图片转代码服务由CSDN问答提供 功能建议

在我的数据库中,我有varchar值表示日期 - 但是我无法将它们作为日期输入到数据库中。 所以,我试图从数据库中提取它们并使用它们来计算天数。

表中的格式:

  mm / dd / yy 
   
 
 

我的SQL语句(在PHP中):

  $ conn = oci_connect(“user”,“pass”,“(description =(address =(protocol = tcp)(host)  = host)(port = 1533))(connect_data =(service_name = sid)))“); 
 
 $ sel =”从INTOXDM.JOINT_USE中选择RECEIVED,其中RECEIVED不为空“; 
 $ 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”); 
  ($今天)$现在=的strtotime;  //或你的日期
 $ your_date = strtotime(“$ date”); 
 $ DAYS =($ now  -  $ your_date)/(60 * 60 * 24); 
 
 
 $ upd =  “更新INTOXDM.JOINT_USE设置DAYS ='$ DAYS',其中RECEIVED ='$ date'”; 
 $ pars = oci_parse($ conn,$ upd); 
 $ exe = oci_execute($ pars);  } 
   
 
 

此代码似乎适用于某些varchars,但许多其他代码都是作为一个荒谬的值传递(16k天)。 这是因为日期被保存为1969年12月30日,我相信。 这表示我的日期编码有问题,但我似乎无法弄明白。

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dtpk04526211 2014-07-15 14:23
    已采纳

    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.

    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题