[php] 날짜 변환 [엑셀형 날짜데이터(43874)] => [m/d/y형] => [sec형(1581865200)] => [y-m-d형]

 [날짜 변환] [엑셀형 날짜데이터(43874)] => [m/d/y형] => [sec형] => [y-m-d형]

echo $row["cons_44"];

=> 43874		//엑셀에서 가져오니 날짜가 이렇게 바뀌었음..

jdtogregorian($row["cons_44"] + 2415023)

=> 2/17/2020

strtotime(jdtogregorian($row["cons_44"] + 2415023))

=> 1581865200

date( 'Y-m-d',  strtotime(jdtogregorian($row["cons_44"] + 2415023)))

=> 2020-02-17

 

[응용] DB 內, [엑셀형 날짜데이터] => [y-m-d형 날짜데이터]

<?php
include '../lib.php';
include '../conn.php';

$dataPath = get_homepath();

$logfile = fopen($dataPath."/ospnavi_20210126/log/datechange_".date("Ymd").".log", "a+");


$sql = "SELECT * FROM cons.cons_datalist";
$sql_obj = $conn->prepare($sql);
$sql_obj->execute();
$result = $sql_obj->get_result();


$check_arr = array();
while($row = $result->fetch_assoc())
{
  $flag = substr($row["cons_44"], 0, 1);;
  //log_file($logfile, "flag: ".$flag);
  if($flag == '4')
  {
    log_file($logfile, "rows[cons_44]: ".$row["cons_44"]);
    $new_date = date( 'Y-m-d',  strtotime(jdtogregorian($row["cons_44"] + 2415023)));
    log_file($logfile, "new_date: ".$new_date);
    $sql1 = "UPDATE cons.cons_datalist SET cons_44 = ? WHERE cons_19 = ?";
    log_file($logfile, "!!!!!YES!!!!!!".$sql1." |  ".$new_date." |  ".$row["cons_19"]);
    $sql_obj1 = $conn->prepare($sql1);
    $sql_obj1->bind_param('ss', $new_date, $row["cons_19"]);
    $sql_obj1->execute();
    $result1 = $sql_obj->get_result();
  }
  //log_file($logfile, "yesorno: ".$yesorno);
  //$factor = explode("/",$row["cons_44"]);
  //log_file($logfile, "factor[0]: ".$factor[0]." "."factor[1]: ".$factor[1]." "."factor[2]: );
} // while($row = $result->fetch_assoc())


?>