Issue with DATETIME field MS-SQL in PHP

Question

I have the following query in MSSQL:

SELECT DataFeed.AccountTranID, DataFeed.Datetime as MyDT
FROM admin_all.DataFeed
where cast(DataFeed.Datetime as Date) = cast(getdate() as Date)

which gives the following output :

AccountTranID   MyDT
124552  2019-07-31 00:00:04.660
124553  2019-07-31 00:00:07.933
124554  2019-07-31 00:00:25.623
124555  2019-07-31 00:00:29.013
124556  2019-07-31 00:00:29.206
124557  2019-07-31 00:00:44.893
124558  2019-07-31 00:00:56.796
124559  2019-07-31 00:01:11.353
124560  2019-07-31 00:01:12.260
124561  2019-07-31 00:01:19.413
124562  2019-07-31 00:01:19.510
124563  2019-07-31 00:01:28.596
124564  2019-07-31 00:01:30.710
124565  2019-07-31 00:01:46.976
124566  2019-07-31 00:01:49.823
124567  2019-07-31 00:01:57.340

when trying to fetch the MyDT field with this PHP code, I cannot get an output (blank). With AccountTranID, no issue.

here goes the Php code:

$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 handling
if ($stmt === false) {
    die(formatErrors(sqlsrv_errors()));
}
?>

<h1> Results : </h1>

<?php

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


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

}

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

the only difference is the use of alias. Just to try do not use the alias for your second element selected — Giacomo M
– Giacomo M, Commented Jul 31, 2019 at 12:18
when trying with no alias "$tsql = "SELECT DataFeed.AccountTranID, DataFeed.Datetime FROM admin_all.DataFeed "; i still get a blank output for $row['Datetime'] — R_life_R
– R_life_R, Commented Jul 31, 2019 at 12:26
i get Array (note: this is a record not included in above table) ( [AccountTranID] => 18784 [Datetime] => DateTime Object ( [date] => 2019-06-29 18:46:07.257000 [timezone_type] => 3 [timezone] => UTC ) ) — R_life_R
– R_life_R, Commented Jul 31, 2019 at 12:31

Giacomo M · Accepted Answer · 2019-07-31 12:41:22Z

5

As we found out, the second selected element of the query is DateTime type, so you have to parse it to string.

You can do with this code:

while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    echo  $row['AccountTranID'].'-'. $row['MyDT']->format('Y-m-d H:i:s').'<br>';
}

edited Jul 31, 2019 at 12:41

answered Jul 31, 2019 at 12:35

Giacomo M

4,7258 gold badges32 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Zhorov · Accepted Answer · 2019-08-01 08:41:11Z

Explanations:

When you retrieve data from SQL Server using sqlsrv_query(), you can control the way the date or date/time values are returned from SQL Server. This can be done by setting 'ReturnDatesAsStrings' option in the connection string. The default value for this option is false and smalldatetime, datetime, date, time, datetime2, and datetimeoffset types are returned as PHP DateTime objects. When this option is true these values are returned as strings.

Retrieve date or date/time values as PHP datetime object:

<?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>";
    }
    ...
?>

Retrieve date or date/time values as string:

<?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>";
    }
    ...
?>

Collectives™ on Stack Overflow

Issue with DATETIME field MS-SQL in PHP

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related