0

........

Where 
  (microsdb.MENU_ITEM_DETAIL.CheckDetailID = microsdb.CHECK_DETAIL.CheckDetailID Or
   microsdb.DISCOUNT_DETAIL.CheckDetailID = microsdb.CHECK_DETAIL.CheckDetailID) And
   microsdb.CHECKS.CheckOpen = CONVERT(CHAR(23), CURRENT_TIMESTAMP, 25)

**Return no result.

Field Data Type

microsdb.CHECKS.CheckOpen (datetime, not null)

CheckOpen 2013-04-08 06:29:26.000

I wondered why my CheckOpen time always 8 hours early than my server time.

Please advise.

Thanks

PeterRing
  • 1,767
  • 12
  • 20
alexcctan
  • 123
  • 1
  • 2
  • 8
  • Try: `SYSUTCDATE` instead of `CURRENT_TIMESTAMP`? `CURRENT_TIMESTAMP` uses the machine's current time-zone. (I'm actually having trouble finding docs about `CheckOpen`) –  Jan 08 '14 at 14:33
  • @ebyrob I think you meant either `SYSUTCDATETIME()` or `GETUTCDATE()`. If we're talking about SQL Server, anyway. Which I'm not sure we are. – Aaron Bertrand Jan 08 '14 at 14:35
  • @AaronBertrand Right, forgot there were hours/seconds on there. (And this one definitely feels MS SQL Server to me, CURRENT_TIMESTAMP + microsdb + style of CONVERT) –  Jan 08 '14 at 14:37

1 Answers1

0

More than likely, when you stored data into the CheckOpen column of your CHECKS table you parsed it (or read it) directly from a client machine or client interface using their time-zone of US/Pacific.

Later, when you read CURRENT_TIME from your DB server you got the system time for that machine in UTC (since the machine was setup to use UTC by your server admin).

So, the two times are 8 hours off. UTC (GMT) is 8 hours ahead of US/Pacific.

Generally, if a client machine gives you data, you need to parse it, validate it, and sometimes translate it to valid server values or be aware when it's stored that it's only a "client" value. For date/time values, either convert to UTC or be sure to store the "offset" with the stored time. (actually it can be good to store offset even after converting to UTC)

  • Yes ebyrob..you are right.My sql server time zone is(UTC+8.00) Kuala Lumper, Singapore. I managed to add 8 hours ahead to my open check field by adding this query SELECT (DATEADD(hh,8,microsdb.CHECKS.CheckOpen) As UTC_OpenCheck_Date. Thank Much ebyrob – alexcctan Jan 14 '14 at 12:24
  • @alexcctan Glad it helped. I'm amazed you weren't in Silicon Valley. Keep in mind, you may have troubles if you have users outside of your Singapore timezone unless you localize the time. see: http://stackoverflow.com/questions/4257442/sql-server-how-to-persist-and-use-a-time-across-different-time-zones –  Jan 14 '14 at 15:00