Dates aren't adjusting for timezone
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 ');
}
?>
sql-server-2012 php-7
asked Nov 12 at 14:15
KVarney
156
add a comment |
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 ');
}
?>
sql-server-2012 php-7
asked Nov 12 at 14:15
KVarney
156
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35
add a comment |
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 ');
}
?>
sql-server-2012 php-7
asked Nov 12 at 14:15
KVarney
156
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 ');
}
?>
sql-server-2012 php-7
sql-server-2012 php-7
asked Nov 12 at 14:15
KVarney
156
asked Nov 12 at 14:15
KVarney
156
asked Nov 12 at 14:15
KVarney
156
asked Nov 12 at 14:15
KVarney
156
asked Nov 12 at 14:15
KVarney
156
156
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35
add a comment |
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35
add a comment |
1 Answer
1
active
oldest
votes
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)
answered Nov 12 at 14:41
Ross Bush
10.8k21941
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264030%2fdates-arent-adjusting-for-timezone%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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)
answered Nov 12 at 14:41
Ross Bush
10.8k21941
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
add a comment |
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)
answered Nov 12 at 14:41
Ross Bush
10.8k21941
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
add a comment |
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)
answered Nov 12 at 14:41
Ross Bush
10.8k21941
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)
answered Nov 12 at 14:41
Ross Bush
10.8k21941
answered Nov 12 at 14:41
Ross Bush
10.8k21941
answered Nov 12 at 14:41
Ross Bush
10.8k21941
answered Nov 12 at 14:41
Ross Bush
10.8k21941
10.8k21941
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
add a comment |
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
Thank you! This was perfect!
– KVarney
Nov 13 at 16:00
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53264030%2fdates-arent-adjusting-for-timezone%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
You probably don't want to hard code the offset because it almost always fluctuates.
– Ross Bush
Nov 12 at 14:21
You mean fluctuating for like daylight saving time? It doesn't actually matter that much. These are inspection reports for a small county government. They just need something more accurate than +5 hours.
– KVarney
Nov 12 at 14:35