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); ?>