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?

Where you have your comment, just put `$row['Date'] = $row['Date']->format('d M Y');` — Nick, Nov 01 '19 at 04:49
@Nick this definitely won't work, i have to declare a variable $date = $row['Date']->format('d M Y') but where do i place the $date since I'm inserting array into my csv and the array is dependent on the selected columns. — Honestman, Nov 01 '19 at 05:10
What I am saying is replace the `DateTime` object in `$row['Date']` with the string representation of that date. I don't understand why that won't work? — Nick, Nov 01 '19 at 05:19
@Nick I'm extremely sorry. I misunderstood your first comment. It works now. Very sorry — Honestman, Nov 01 '19 at 05:22
@Nick another problem on top of this. after converting the date, it appears as a different format in excel when exported. I convert this date format $row['Date'] = $row['Date']->format('M-y') but Excel is showing this format '19-Oct' and it changes my raw data from '2019-10-01' to '2019-10-19'. Why is this happening? — Honestman, Nov 01 '19 at 05:37
You should probably use the full format date i.e. `$row['Date']->format('Y-m-d')` Excel should parse that properly — Nick, Nov 01 '19 at 05:53

Zhorov · Answer 1 · 2019-11-06T07:18:43.550

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);
    }   
    ...

?>

Object of class DateTime error when i export data to excel

1 Answers1