PHP7.3 SQLSRV - 查询和存储时日期时间的格式不同

Question

我们必须升级系统以使用 PHP7.3(从 MSSQL 更改为 SQLSRV DB Driver 和带有 PDO 的 SQLSRV)

我的问题是，我不理解这种行为以及如何正确解决它。

当我从数据库查询 DateTime 列时，我得到:

2019-03-26 00:00:00.000

当我尝试将相同的值存储到数据库的 DateTime 列中时，它不起作用。我的数据库接受这种格式，例如:

 26-03-2019 00:00:00.000

因此，我们现有的数据库层无法正常工作。

我需要帮助来理解这种行为，以及如何告诉数据库使用相同的格式进行查询和插入/更新

我们使用的查询没有特殊的转换，简单的 SELECT/UPDATE/INSERT。

PHP 版本:PHP 7.3.3-1+ubuntu18.04.1+deb.sury.org+1 (cli)(构建时间:2019 年 3 月 7 日 20:31:49)(NTS)
版权所有 (c) 1997-2018 PHP 集团Zend Engine v3.3.3，版权所有 (c) 1998-2018 Zend Technologies使用 Zend OPcache v7.3.3-1+ubuntu18.04.1+deb.sury.org+1，版权所有 (c) 1999-2018，Zend Technologies

SQL服务器:MSSQL Server 2014

感谢任何提示

Answer 1

<强>1。如何从 SQL Server 检索日期和时间值:

1.1。使用 PHP Driver for SQL Server 的 PDO_SQLSRV 版本从 SQL Server 检索日期和时间值:

在驱动程序版本 5.6.0 之前，当您使用 PDO_SQLSRV 驱动程序时，日期和时间列中的值将以字符串形式返回。在这种情况下，唯一的选择是重新格式化日期和时间值(例如从 '2019-03-26 00:00:00.000' 到 '26-03-2019 00:00:00.000')。从版本 5.6.0 开始，您可以使用 PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE 更改此行为连接或语句属性并检索日期和时间值作为 PHP DateTime 变量。然后您可以使用 DateTime::format 轻松地将此变量格式化为字符串。 .

<?php

// Connection
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$conn = new PDO("sqlsrv:server=$server;Database=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);

// Datetime values as PHP DateTime object
$query = "SELECT GETDATE() AS DateTimeColumn";
$stmt = $conn->prepare($query);
$stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, true);
$stmt->execute();

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
   echo $row["DateTimeColumn"]->format('Y-m-d H:i:s.v') . "<br>";
}
$stmt = null;

// Datetime values as text
$query = "SELECT GETDATE() AS DateTimeColumn";
$stmt = $conn->prepare($query);
$stmt->setAttribute(PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE, false);
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { 
   echo $row["DateTimeColumn"] . "<br>";
}
$stmt = null;

// End
$conn = null;

?>

1.2。使用 PHP Driver for SQL Server 的 PDO_SQLSRV 版本从 SQL Server 存储过程检索输出参数的日期和时间值:

文档中的注释解释说 PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE 连接或语句属性仅适用于定期获取日期和时间类型，因为无法将 DateTime 对象指定为输出参数。在这种情况下，唯一可能的选择是将存储过程的日期时间输出参数作为 PHP 字符串变量传递。返回值取决于 session 的语言环境。

存储过程:

CREATE PROCEDURE spReturnDateTime
    @datetime datetime OUTPUT
AS
BEGIN
    SET NOCOUNT ON
    SELECT GETDATE() AS [DateTime]
    SET @datetime = GETDATE()
END

PHP:

<?php
// Connnection
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$conn = new PDO("sqlsrv:server=$server;Database=$database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$conn->setAttribute(PDO::SQLSRV_ATTR_ENCODING, PDO::SQLSRV_ENCODING_SYSTEM);

// Output parameters from stored procedure
// 
$sql = "
    SET LANGUAGE 'English'
    EXEC :errcode = spReturnDateTime @datetime = :datetime
";
$errcode = 0;
$datetime = "";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':errcode', $errcode, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
$stmt->bindParam(':datetime', $datetime, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 50);
$stmt->execute();
do {
    while ($row = $stmt->fetch( PDO::FETCH_ASSOC) ){
        echo $row["DateTime"]."<br>";
    }
} while ($stmt->nextRowset());  
$stmt = null;
echo $datetime."<br>";
//
$sql = "
    SET LANGUAGE 'Bulgarian'
    EXEC :errcode = spReturnDateTime @datetime = :datetime
";
$errcode = 0;
$datetime = "";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':errcode', $errcode, PDO::PARAM_INT | PDO::PARAM_INPUT_OUTPUT, PDO::SQLSRV_PARAM_OUT_DEFAULT_SIZE);
$stmt->bindParam(':datetime', $datetime, PDO::PARAM_STR | PDO::PARAM_INPUT_OUTPUT, 50);
$stmt->execute();
do {
    while ($row = $stmt->fetch( PDO::FETCH_ASSOC) ){
        echo $row["DateTime"]."<br>";
    }
} while ($stmt->nextRowset());  
$stmt = null;
echo $datetime."<br>";

// End
$conn = null;

结果:

2021-06-10 10:05:54.580
Jun 10 2021 10:05AM
2021-06-10 10:05:54.580
юни 10 2021 10:05AM

1.3。使用 PHP Driver for SQL Server 的 SQLSRV 版本从 SQL Server 检索日期和时间值:

默认情况下，smalldatetime、datetime、date、time、datetime2 和 datetimeoffset 类型将作为 PHP DateTime 对象返回，但可以通过设置 'ReturnDatesAsStrings' 来更改此行为。连接字符串或语句级别的选项:

<?php

// Connection
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

// Datetime values as PHP DateTime object
$query = "SELECT GETDATE() AS DateTimeColumn";
$options = array('ReturnDatesAsStrings' => false);
$stmt = sqlsrv_query($conn, $query, null, $options);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
   echo $row["DateTimeColumn"]->format('Y-m-d H:i:s.v') . "<br>";
}
sqlsrv_free_stmt($stmt);

// Datetime values as text
$query = "SELECT GETDATE() AS DateTimeColumn";
$options = array('ReturnDatesAsStrings' => true);
$stmt = sqlsrv_query($conn, $query, null, $options);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
   echo $row["DateTimeColumn"] . "<br>";
}
sqlsrv_free_stmt($stmt);

// End
sqlsrv_close($conn);

?>

1.4。使用 MSSQL PHP 扩展从 SQL Server 检索日期和时间值(该扩展在 PHP 7.0.0 中已删除):

当您使用 MSSQL 扩展时，日期和时间值将以文本形式返回，但格式取决于 mssql.datetimeconvert设置在php.ini文件。当此选项为ON时，日期和时间值根据 SQL Server 设置进行转换，何时为 OFF日期和时间值转换为 YYYY-MM-DD hh:mm:ss格式。

<?php
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$conn = mssql_connect($server);
if ($conn === false) {
    echo "Error (mssql_connect): ".mssql_get_last_message();
    exit;
}
mssql_select_db($database, $conn);

$query = "SELECT DateTimeColumn FROM OneTable";
$stmt = mssql_query($sql, $conn);
if ($stmt === false) {
    echo "Error (mssql_query): ".mssql_get_last_message();
    exit;
}

while ($row = mssql_fetch_assoc($stmt)) {
   echo print_r($row, true);
}

mssql_free_result($stmt);
mssql_close($conn);
?>

作为补充说明，此设置似乎是 ON在您的服务器环境中，因为您可以发送类似 '26-03-2019 00:00:00.000' 的日期没有错误。

<强>2。如何将日期和时间值传递给 SQL Server:

作为一般规则，可以使用明确的日期时间格式( yyyymmdd 或 yyyy-mm-ddThh:mm:ss )和参数化语句将日期和时间值传递到 SQL Server。

2.1。使用 PHP Driver for SQL Server 的 PDO_SQLSRV 版本将日期和时间值传递给 SQL Server:

<?php
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$conn = new PDO("sqlsrv:server = $server; Database = $database", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
$datetime = (new DateTime())->format("Y-m-d\TH:i:s");
$stmt = $conn->prepare($query);
$stmt->bindParam(1, $datetime);
$stmt->execute();

$stmt = null;
$conn = null;
?>

2.2。使用 PHP Driver for SQL Server 的 SQLSRV 版本将日期和时间值传递给 SQL Server:

使用此版本的驱动程序，您可以使用扩展参数语法并将日期时间值作为 PHP DateTime 对象传递，其中包含有关参数的 PHP 和 SQL Server 数据类型的信息。

<?php
$server   = "server\instanse";
$database = "database";
$username = "username";
$password = "password";

$cinfo = array(
   "Database" => $database, 
   "UID" => $username, 
   "PWD" => $password
);
$conn = sqlsrv_connect($server, $cinfo);
if ($conn === false) {
   echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
   exit;
}

$query = "INSERT INTO OneTable (DateTimeColumn) VALUES (?)";
$datetime = new DateTime();
$params = array(
   array($datetime, SQLSRV_PARAM_IN, SQLSRV_PHPTYPE_DATETIME, SQLSRV_SQLTYPE_DATETIME)
); 
// or as usual, pass datetime values as text
//$params = array($datetime->format("Y-m-d\TH:i:s")); 
$stmt = sqlsrv_query($conn, $query, $params);
if ($stmt === false) {
   echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
   exit;
}

sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>