Object of class DateTime error when i export data to excel

Question

I have a table which contain many columns. Some column contain numerical value, some contain text while some contain date.

When i try to export data to Excel, the numerical and value can be exported however when i want to export date data, it shows me this error "Object of class DateTime could not be converted to string".

My export function works in such a way that user can select which column they wan to export from SQL to excel through PHP. My column are stored in array. Below is my code:

   //Get list of selected column from selected checkboxes
    $colarray = $_GET['colarray'];

    //convert string to array 
    $string1 = ['[',']'];
    $string2 = ["",""];
    $newcolarray = str_replace($string1, $string2, $colarray);
    $newarray = explode(",",$newcolarray);

    $filename = "File name " . date('d-M-y') . ".csv"; // Create file name

    $f = fopen('php://memory', 'w');

//Insert column name in first row of excel
    fputcsv($f, $newarray);

    //dynamic Select query statement
    $query = "SELECT "; 
    $query .= $colarray;
    $query .= " FROM OVERALL_SUMMARY_ORIGINAL"; // Get data from Database from OVERALL_SUMMARY_ORIGINAL table
    $stmt = sqlsrv_query($conn, $query);

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

/**how can i place this code $row['Date']->format('d M Y') here***/ 
        fputcsv($f, $row);

    }

    //move back to beginning of file
    fseek($f, 0);

    //set headers to download file rather than displayed
    header('Content-Type: text/csv');
    header('Content-Disposition: attachment; filename="' . $filename . '";');

    //output all remaining data on a file pointer
    fpassthru($f);

Can anyone help how i can convert my date data into a string since my columns are stored in array for exporting to csv?

Answer 1

With PHP Driver for SQL Server you may retrieve PHP Date and Time objects as strings or as DateTime objects using the 'ReturnDatesAsStrings' option in the connection string or at the statement level.

If I understand your case correctly, the columns in the SQL statement are dynamic. In this case you may try to use this feature at the statement level.

<?php

    ...
    $query = "SELECT "; 
    $query .= $colarray;
    $query .= " FROM OVERALL_SUMMARY_ORIGINAL"; // Get data from Database from OVERALL_SUMMARY_ORIGINAL table
    $options = array('ReturnDatesAsStrings' => true);
    $stmt = sqlsrv_query($conn, $query, array(), $options);
    if ($stmt === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }
    while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
        fputcsv($f, $row);
    }   
    ...

?>  

Another option, of course, is to format the PHP DateTime object using DateTime::format method, but after you check that these values are PHP DateTime objects.

<?php

    ...
    $query = "SELECT "; 
    $query .= $colarray;
    $query .= " FROM OVERALL_SUMMARY_ORIGINAL"; // Get data from Database from OVERALL_SUMMARY_ORIGINAL table
    $stmt = sqlsrv_query($conn, $query);
    if ($stmt === false) {
        echo "Error (sqlsrv_query): ".print_r(sqlsrv_errors(), true);
        exit;
    }
    while($row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC)){
        foreach($row as $key => $value) {
            if ($value instanceof DateTime) {
                $row[$key] = $row[$key]->format('d M Y')
            }
        }
        fputcsv($f, $row);
    }   
    ...

?>