I'm trying to create a simple table displaying data from June 2019, however i'm having trouble getting the MSSQL datetime row to echo out correctly whenever i remove my var_dump line.
So with the var_dump i get the correct dates but without all my columns show up as a date from 1970 which doesn't exist, however when i do a vardump of the Transaction date first, i get the correct result on all lines??
Is this something to do with me needing to change the format of the row to a format that PHP can understand?
Here's an example of the input 2016-07-04 00:00:00.000
Below is my code.
<?php
$serverName = "SQL,1433";
$connectionInfo = array( "Database"=>"", "UID"=>"", "PWD"=>"");
$conn = sqlsrv_connect( $serverName, $connectionInfo );
$sql = "SELECT TOP 1 * FROM [all sales data]";
$stmt = sqlsrv_query( $conn, $sql);
while( $row = sqlsrv_fetch_array( $stmt, SQLSRV_FETCH_ASSOC) ) {
?>
<?php var_dump( $row['Transaction Date']);?>
<tr>
<td><?php echo $row['Order Point Account'];?></td>
<td><?php echo $row['Analysis Category'];?></td>
<td><?php echo $row['Branch Code'];?></td>
<td><?php echo date("d m Y", strtotime($row['Transaction Date']->date));?></td>
<td><?php echo $row['Product Discount Group'];?></td>
<td><?php echo $row['End Product Cost of Sale'];?></td>
<td><?php echo $row['End Product Other Value'];?></td>
<td><?php echo $row['End Product Sales Value'];?></td>
<td><?php echo $row['End Product Analysis Quantity'];?></td>
<td><?php echo $row['Invoice Account'];?></td>
<td><?php echo $row['Magic Number'];?></td>
<td><?php echo $row['Order Number'];?></td>
<td><?php echo $row['PAC Level 1'];?></td>
<td><?php echo $row['PAC Level 2'];?></td>
<td><?php echo $row['PAC Level 3'];?></td>
<td><?php echo $row['PAC Level 4'];?></td>
<td><?php echo $row['Product Code'];?></td>
<td><?php echo $row['Salesman 1'];?></td>
<td><?php echo $row['Statement Account'];?></td>
<td><?php echo $row['Supplier Account'];?></td>
<td><?php echo $row['Contact']?></td>
<td><?php echo $row['Order Source'];?></td>
<td><?php echo $row['User'];?></td>
<td><?php echo $row['Customer Order No'];?></td>
</tr>
<?php
}
?>
</tbody>
</table>
</body>
</html>
below is the vardump
object(DateTime)#1 (3) { ["date"]=> string(26) "2016-07-04 00:00:00.000000" ["timezone_type"]=> int(3) ["timezone"]=> string(13) "Europe/London" }
Here's the result of print_r($row);
Array ( [Order Point Account] => [Analysis Category] => AD [Branch Code] => 100 [Transaction Date] => DateTime Object ( [date] => 2016-07-04 00:00:00.000000 [timezone_type] => 3 [timezone] => Europe/London ) [Product Discount Group] => [End Product Cost of Sale] => 165.19999694824 [End Product Other Value] => 165.2 [End Product Sales Value] => 369.60000610352 [End Product Analysis Quantity] => 1 [Invoice Account] => [Magic Number] => 800713 [Sales Order Line Number] => [Order Number] => 319651 [PAC Level 1] => d [PAC Level 2] => dLB [PAC Level 3] => dLB01 [PAC Level 4] => dLB0106 [Product Code] => [Salesman 1] => 9500 [Statement Account] => [Supplier Account] => [Contact] => fax [Order Source] => FAX [User] => [Customer Order No] => )
When you use PHP driver for SQL Server, not PDO version, you can retrieve date and time types (datetime, date, time, datetime2, and datetimeoffset) as strings or as PHP datetime objects by specifying ReturnDatesAsStrings option in the connection string:
<?php
...
$serverName = "SQL,1433";
$connectionInfo = array(
"ReturnDatesAsStrings"=>false,
// Set to TRUE if you want to get date values as string.
//"ReturnDatesAsStrings"=>true,
"Database"=>'database',
"UID"=>"username",
"PWD"=>"password"
);
$conn = sqlsrv_connect( $serverName, $connectionInfo );
...
?>
By default, ReturnDatesAsStrings is false, so you just need to format datetime object.
<?php
...
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
...
// Format date with procedural or object oriented style:
<td><?php echo date_format($row['Transaction Date'], 'd m Y');?></td>
<td><?php echo $row['Transaction Date']->format('d m Y');?></td>
...
}
...
?>