PHP 中 DATETIME 字段 MS-SQL 的问题

Question

我在 MSSQL 中有以下查询：SELECT DataFeed.AccountTranID, DataFeed.Datetime as MyDTFROM admin_all.DataFeedwhere cast(DataFeed.Datetime as Date) = cast(getdate() as Date)这给出了以下输出：AccountTranID   MyDT124552  2019-07-31 00:00:04.660124553  2019-07-31 00:00:07.933124554  2019-07-31 00:00:25.623124555  2019-07-31 00:00:29.013124556  2019-07-31 00:00:29.206124557  2019-07-31 00:00:44.893124558  2019-07-31 00:00:56.796124559  2019-07-31 00:01:11.353124560  2019-07-31 00:01:12.260124561  2019-07-31 00:01:19.413124562  2019-07-31 00:01:19.510124563  2019-07-31 00:01:28.596124564  2019-07-31 00:01:30.710124565  2019-07-31 00:01:46.976124566  2019-07-31 00:01:49.823124567  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 handlingif ($stmt === false) {    die(formatErrors(sqlsrv_errors()));}?><h1> Results : </h1><?phpwhile ($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)) {

    echo  $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

    // Connection

    $server = '127.0.0.1\ikosoft,1066';

    $cinfo = array(

        "ReturnDatesAsStrings"=>false,

        "Database" => "database",

        "UID" => "username",

        "PWD" => "password"

    );

    $conn = sqlsrv_connect($server, $cinfo);

    if( $conn === false )

    {

        echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);

        exit;

    }

    ....

    // Fetch data

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

        echo  $row['MyDT']->format('Y-m-d H:i:s')."<br>";

    }

    ...

?>

检索date或date/time值作为字符串：

<?php

    // Connection

    $server = '127.0.0.1\ikosoft,1066';

    $cinfo = array(

        "ReturnDatesAsStrings"=>true,

        "Database" => "database",

        "UID" => "username",

        "PWD" => "password"

    );

    $conn = sqlsrv_connect($server, $cinfo);

    if( $conn === false )

    {

        echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);

        exit;

    }

    ....

    // Fetch data

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

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

    }

    ...

?>