Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

How to: Retrieve Date and Time Type as Strings Using the SQLSRV Driver

SQL Server 2008 R2

This feature was added in version 1.1 of the Microsoft Drivers for PHP for SQL Server and is only valid when using the SQLSRV driver for the Microsoft Drivers for PHP for SQL Server. It is an error to use the ReturnDatesAsStrings connection option with the PDO_SQLSRV driver.

You can retrieve date and time types (datetime, date, time, datetime2, and datetimeoffset) as strings by specifying an option in the connection string.

To retrieve date and time types as strings

  • Use the following connection option:

    'ReturnDatesAsStrings'=>true
    

    The default is false, which means that datetime, Date, Time, DateTime2, and DateTimeOffset types will be returned as PHP Datetime types.

The following example shows the syntax specifying to retrieve date and time types as strings.

<?php
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", 'ReturnDatesAsStrings '=> true);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false )
{
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}

sqlsrv_close( $conn);
?>

The following example shows that you can retrieve dates as strings by specifying UTF-8 when you retrieve the string, even when the connection was made with "ReturnDatesAsStrings" => false.

<?php
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", "ReturnDatesAsStrings" => false);
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}

$tsql = "SELECT VersionDate FROM AWBuildVersion";

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

if ( $stmt === false ) {
   echo "Error in statement preparation/execution.\n";
   die( print_r( sqlsrv_errors(), true));
}

sqlsrv_fetch( $stmt );

// retrieve date as string
$date = sqlsrv_get_field( $stmt, 0, SQLSRV_PHPTYPE_STRING("UTF-8"));

if( $date === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

echo $date;

sqlsrv_close( $conn);
?>

The following example shows how to retrieve dates as strings by specifying UTF-8 and "ReturnDatesAsStrings" => true in the connection string.

<?php
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks", 'ReturnDatesAsStrings'=> true, "CharacterSet" => 'utf-8' );
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}

$tsql = "SELECT VersionDate FROM AWBuildVersion";

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

if ( $stmt === false ) {
   echo "Error in statement preparation/execution.\n";
   die( print_r( sqlsrv_errors(), true));
}

sqlsrv_fetch( $stmt );

// retrieve date as string
$date = sqlsrv_get_field( $stmt, 0 );

if ( $date === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

echo $date;
sqlsrv_close( $conn);
?>

The following example shows how to retrieve the date as a PHP type. 'ReturnDatesAsStrings'=> false is on by default.

<?php
$serverName = "MyServer";
$connectionInfo = array( "Database"=>"AdventureWorks");
$conn = sqlsrv_connect( $serverName, $connectionInfo);
if( $conn === false ) {
   echo "Could not connect.\n";
   die( print_r( sqlsrv_errors(), true));
}

$tsql = "SELECT VersionDate FROM AWBuildVersion";

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

if ( $stmt === false ) {
   echo "Error in statement preparation/execution.\n";
   die( print_r( sqlsrv_errors(), true));
}

sqlsrv_fetch( $stmt );

// retrieve date as string
$date = sqlsrv_get_field( $stmt, 0 );

if ( $date === false ) {
   die( print_r( sqlsrv_errors(), true ));
}

$date_string = date_format( $date, 'jS, F Y' );
echo "Date = $date_string\n";

sqlsrv_close( $conn);
?>

Community Additions

ADD
Show:
© 2015 Microsoft