Skip to content
Advertisement

ReturnDatesAsStrings=>true returns timestamp with decimal & zeros

I’m using PHP 7.2, MS SQL Server, fpdf, and phpSpreadsheet to create PDF and Excel reports of my data. Initially I had trouble returning the timestamp (date) fields in the reports, but adding "ReturnDatesAsStrings"=>true in my connection.php solved that problem.

The issue now is that the date fields on the reports have a decimal and zeros added and show up as 2018-03-01 16:43:19.0000000. How can I stop the addition of .0000000? I’m not sure if it makes it any more complicated that it also needs to work with fpdf and phpSpreadsheet, but I included that info just in case.

UPDATE: Zhorov’s first answer was right. I removed "ReturnDatesAsStrings"=>true from connection.php and the phpSpreadsheet files work great! On the fpdf files, I had to add in ‘$date_as_string = date_format($data[‘created_date’], ‘Y-m-d H:i:s’);’ and then the PDFs work too.

FINAL SOLUTION: NULL values were causing a problem, but Zhorov’s fix worked. Had to add $date_as_string = is_null($row['DateField']) ? '' : date_format($row['DateField'] , 'Y-m-d H:i:s');

Advertisement

Answer

Use ReturnDatesAsStrings => false connection option and date_format() PHP function.

When ReturnDatesAsStrings is false, datetime, Date, Time, DateTime2, and DateTimeOffset types will be returned as PHP Datetime types. So format these fields as PHP dates with custom format.

<?php

# Connection
$server = 'serverinstance,port';
$cinfo = array(
    "ReturnDatesAsStrings"=>false,
    "Database"=>'database',
    "UID"=>"uid",
    "PWD"=>"pwd"
);
$conn = sqlsrv_connect($server, $cinfo);
if( $conn === false )
{
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}

# Statement
...
$sql = 'SELECT [DateField] FROM [Table]';
$stmt = sqlsrv_query($conn, $sql);  
if ($stmt === false) {
    echo "Error (sqlsrv_connect): ".print_r(sqlsrv_errors(), true);
    exit;
}
while($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
    $date_as_string = is_null($row['DateField']) ? '' : date_format($row['DateField'] , 'Y-m-d H:i:s');

    # I'm not familiar with PhpSpreadsheet, but based on documantation, this should work.
    $spreadsheet->getActiveSheet()->setCellValue('A'.$row , $data->OBJECTID);
    $spreadsheet->getActiveSheet()->setCellValue('B'.$row , $data->Inspector);
    $spreadsheet->getActiveSheet()->setCellValue('C'.$row , $date_as_string);

    echo $date_as_string;
    echo '</br>';
}
...

# End
sqlsrv_free_stmt($stmt);
sqlsrv_close($conn);
?>
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement