Myujth

INSPDATE is a datetime field in an MS SQL database. Normally when we pull in a date, the browser automatically corrects it to our EST, but it doesn't work on this inspection report I'm creating. I've tried

echo date('m/d/Y g:i a ', strtotime($row['INSPDATE']));

But that returns the date from 1970 (12/31/1969 7:00 pm) and isn't pulling in the value from the database.

Below is what I was using that returns the date just as it's saved in the db. I know there's a way to subtract 18000 to this to adjust to EST (-5 hrs), but I'm not sure where to put it. I get error messages about mixing up datetimes and non-numeric values.

<?php

require_once ('connection.php');

$sql = "SELECT INSPDATE, FACILID FROM dbo.SWFACILITYINSPECTION WHERE FACILID = 'E667'";
$query = sqlsrv_query( $conn, $sql );
while ($row=sqlsrv_fetch_array( $query )) 

//This will show dates, but it's database time (UTC, so +5 hours)
echo date_format($row['INSPDATE'], 'm/d/Y g:i a ');



?>

You should always do this conversion in application logic, since your sql is inline, I suppose you could embed the calculation in your sql. If I knew PHP better, I would be more inclined to learn the date math using the language of the application, that way you could build a time zone library to use on dates elsewhere and free your queries,store procedures up to be, just queries and stored procedures that return data.

DECLARE @Offset INT = -5

SELECT INSPDATE_UTC=INSPDATE, INSPDATE_LOCAL = DATEADD(HOUR, @Offset,INSPDATE)