PHP 中 DATETIME 字段 MS-SQL 的问题

Question

我在 MSSQL 中有以下查询：

SELECT DataFeed.AccountTranID, DataFeed.Datetime as MyDT

FROM admin_all.DataFeed

where cast(DataFeed.Datetime as Date) = cast(getdate() as Date)

这给出了以下输出：

AccountTranID   MyDT

124552  2019-07-31 00:00:04.660

124553  2019-07-31 00:00:07.933

124554  2019-07-31 00:00:25.623

124555  2019-07-31 00:00:29.013

124556  2019-07-31 00:00:29.206

124557  2019-07-31 00:00:44.893

124558  2019-07-31 00:00:56.796

124559  2019-07-31 00:01:11.353

124560  2019-07-31 00:01:12.260

124561  2019-07-31 00:01:19.413

124562  2019-07-31 00:01:19.510

124563  2019-07-31 00:01:28.596

124564  2019-07-31 00:01:30.710

124565  2019-07-31 00:01:46.976

124566  2019-07-31 00:01:49.823

124567  2019-07-31 00:01:57.340

尝试使用此 PHP 代码获取 MyDT 字段时，我无法获得输出（空白）。使用 AccountTranID，没问题。

这是 PHP 代码：

$tsql = "SELECT DataFeed.AccountTranID, DataFeed.Datetime as MyDT 

FROM admin_all.DataFeed where cast(DataFeed.Datetime as Date) = cast(getdate() as Date) ";

// Executes the query

$stmt = sqlsrv_query($conn, $tsql);

// Error handling

if ($stmt === false) {

    die(formatErrors(sqlsrv_errors()));

}

?>

<h1> Results : </h1>

<?php

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {

    echo  $row['AccountTranID'].'-'. $row['MyDT'].'<br>';

}

sqlsrv_free_stmt($stmt);

sqlsrv_close($conn);

Answer 1

正如我们发现的，查询的第二个选定元素是 DateTime 类型，因此您必须将其解析为字符串。您可以使用以下代码：while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {&nbsp; &nbsp; echo&nbsp; $row['AccountTranID'].'-'. $row['MyDT']->format('Y-m-d H:i:s').'<br>';}

Answer 2

说明：当您使用 检索 SQL Server 数据时sqlsrv_query()，您可以控制从 SQL Server返回date或date/time值的方式。这可以通过在连接字符串中设置选项来完成。此选项的默认值是and 、、、、、 和类型作为 PHP DateTime 对象返回。使用此选项时，这些值将作为字符串返回。'ReturnDatesAsStrings'falsesmalldatetimedatetimedatetimedatetime2datetimeoffsettrue检索date或date/time值作为 PHP 日期时间对象：<?php&nbsp; &nbsp; // Connection&nbsp; &nbsp; $server = '127.0.0.1\ikosoft,1066';&nbsp; &nbsp; $cinfo = array(&nbsp; &nbsp; &nbsp; &nbsp; "ReturnDatesAsStrings"=>false,&nbsp; &nbsp; &nbsp; &nbsp; "Database" => "database",&nbsp; &nbsp; &nbsp; &nbsp; "UID" => "username",&nbsp; &nbsp; &nbsp; &nbsp; "PWD" => "password"&nbsp; &nbsp; );&nbsp; &nbsp; $conn = sqlsrv_connect($server, $cinfo);&nbsp; &nbsp; if( $conn === false )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);&nbsp; &nbsp; &nbsp; &nbsp; exit;&nbsp; &nbsp; }&nbsp; &nbsp; ....&nbsp; &nbsp; // Fetch data&nbsp; &nbsp; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {&nbsp; &nbsp; &nbsp; &nbsp; echo&nbsp; $row['MyDT']->format('Y-m-d H:i:s')."<br>";&nbsp; &nbsp; }&nbsp; &nbsp; ...?>检索date或date/time值作为字符串：<?php&nbsp; &nbsp; // Connection&nbsp; &nbsp; $server = '127.0.0.1\ikosoft,1066';&nbsp; &nbsp; $cinfo = array(&nbsp; &nbsp; &nbsp; &nbsp; "ReturnDatesAsStrings"=>true,&nbsp; &nbsp; &nbsp; &nbsp; "Database" => "database",&nbsp; &nbsp; &nbsp; &nbsp; "UID" => "username",&nbsp; &nbsp; &nbsp; &nbsp; "PWD" => "password"&nbsp; &nbsp; );&nbsp; &nbsp; $conn = sqlsrv_connect($server, $cinfo);&nbsp; &nbsp; if( $conn === false )&nbsp; &nbsp; {&nbsp; &nbsp; &nbsp; &nbsp; echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);&nbsp; &nbsp; &nbsp; &nbsp; exit;&nbsp; &nbsp; }&nbsp; &nbsp; ....&nbsp; &nbsp; // Fetch data&nbsp; &nbsp; while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {&nbsp; &nbsp; &nbsp; &nbsp; echo&nbsp; $row['MyDT']."<br>";&nbsp; &nbsp; }&nbsp; &nbsp; ...?>