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