PHP7.3 SQLSRV - Format of DateTime different when Query and Store

We have to upgrade our system to use PHP7.3 (change from MSSQL to SQLSRV DB Driver and SQLSRV with PDO)

My problem is, that i dont understand this behavior and how to solve it properly.

When i query a DateTime column from Database i get:

2019-03-26 00:00:00.000

When i try to store the same value into a DateTime column into the database it doesn't work. My Database accept this format for example:

 26-03-2019 00:00:00.000

for that reason our existing database layer is not working properly.

i need help to understand this behavior, and how to tell the database to use the same format for Query and INSERT/UPDATE

The Querys we used have no special conversions, Simple SELECT / UPDATE / INSERT.

PHP Version: PHP 7.3.3-1+ubuntu18.04.1+deb.sury.org+1 (cli) (built: Mar 7 2019 20:31:49) ( NTS )
Copyright (c) 1997-2018 The PHP Group Zend Engine v3.3.3, Copyright (c) 1998-2018 Zend Technologies with Zend OPcache v7.3.3-1+ubuntu18.04.1+deb.sury.org+1, Copyright (c) 1999-2018, by Zend Technologies

SQLServer: MSSQL Server 2014

thx for any hint

Solution

1. How to retrieve Date and Time values from SQL Server:

1.1. Retrieve Date and Time values from SQL Server using the PDO_SQLSRV version of PHP Driver for SQL Server:

When you use PDO_SQLSRV driver, values from date and time columns are returned as strings before version 5.6.0 of the driver. In this case the only option is to reformat date and time values (from '2019-03-26 00:00:00.000' to '26-03-2019 00:00:00.000' for example). Starting with version 5.6.0, you can change this behavior using PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE connection or statement attribute and retrieve date and time values as PHP DateTime variable. Then you can easily format this variable as string using 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. Retrieve Date and Time values of output parameters from SQL Server stored procedure uisng the PDO_SQLSRV version of PHP Driver for SQL Server:

A note from the documentation explains, that PDO::SQLSRV_ATTR_FETCHES_DATETIME_TYPE connection or statement attribute only applies to regular fetching of date and time types because DateTime objects cannot be specified as output parameters. In this situation, the only possible option is to pass the datetime output parameter of the stored procedure as a PHP string variable. The return value depends on the language environment for the session.

Stored procedure:

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;

Results:

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. Retrieve Date and Time values from SQL Server using the SQLSRV version of PHP Driver for SQL Server:

By default smalldatetime, datetime, date, time, datetime2 and datetimeoffset types will be returned as PHP DateTime objects, but this behaviour can be changed by setting the 'ReturnDatesAsStrings' option in the connection string or at the statement level:

<?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. Retrieve Date and Time values from SQL Server using MSSQL PHP extension (the extension was removed in PHP 7.0.0):

When you use MSSQL extension, date and time values are returned as text, but the format depends on mssql.datetimeconvert setting in php.ini file. When this options is ON, date and time values are converted based on SQL Server settings, and when is OFF date and time values are converted to YYYY-MM-DD hh:mm:ss format.

<?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);
?>

As an additional note, it seems that this setting is ON in your server environment, because you can send dates like '26-03-2019 00:00:00.000' without errors.

2. How to pass Date and Time values to SQL Server:

As a general rule, date and time values can be passed to SQL Server using unambiguous datetime format (yyyymmdd or yyyy-mm-ddThh:mm:ss) and parameterized statement.

2.1. Pass Date and Time values to SQL Server uisng the PDO_SQLSRV version of PHP Driver for 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. Pass Date and Time values to SQL Server using the SQLSRV version of PHP Driver for SQL Server:

With this version of the driver, you may use the extended parameters syntax and pass the datetime value as PHP DateTime object with information about the PHP and SQL Server data types of the parameter.

<?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);
?>