Time from Backend

[twgonder]

I'm trying to determine the time at the backed computer on the network, in both local and UTC. I know Net Time \\wsname will get the local time, but I'm not sure how to get it into a variable in VBA, nor how to get either the offset or UTC.



Got any ideas? Thanks

[twgonder]

After a bit of digging around in another forum, I found this:

Function GetTimeFromServer(svrName As String) As String
  GetTimeFromServer = CreateObject("WScript.Shell").Exec("Net Time \\" & svrName).StdOut.ReadAll
End Function

And it works for the local time. Which is a start, but I still need to check the offset or UTC time for that computer.

[Minty]

Interesting problem. I think you would have to run a script on that machine.

What about putting a timer event on the backend database to dump the current local time into a table, link to that you have your server time?
Set the time interval to however accurate you need the time to be?

[twgonder]

@ Minty That was one of the possibilities, I'm adverse to having processes running on the BE that require some kind of user intervention (such as after a restart).
Do developers that use one of the various flavors of a SQL Server have a similar problem?
The possibility of a BE serving more than one time zone is the catalyst of considering storing some dates as UTC, but it quickly becomes a quite complex problem.

[CJ_London]

You could just store the difference between utc and local time in a config table then add it to server time. Plenty of sites out there to get the data. Config table would also need to store the dates for DSH (daylight saving hours)

Only time that wouldn’t work is if the user travels between time zones.

[NoellaG]

For SQL server you have several functions:
examples:

select current_timezone() -- gives current timezone
select getdate()  -- get current date + time
select getutcdate()  --get current UTC date + time
select datepart(HH,getdate()- getutcdate() ) --get time difference between current zone and UTC time in hours

[twgonder]

@CJ_London. #5 I've got a network resource table that has all the time offsets. And I use that in the application.
The problem is confirming at login that the BE is what I think it should be. For now, I've fudged it around a bit to use the BE local time.
Yes, I am having fun working around users that can move between time zones. The real fun is in the time tracking modules where it's a mess to know the time they started and stopped a task.
And what DSL can do with night shifts at the factory. For that I'm considering UTC, but that poses a whole new issue, as to what fields to use UTC and not use it.
UTC is good for time tracking, it eliminates all the TZ and DSL problems. But what about things like birthdates? Do we want the date/time to jump around based on TZ?
Old man time gets us in so many ways.

[pbaldy]

Do developers that use one of the various flavors of a SQL Server have a similar problem?

I have one Azure db where GetDate() returns the incorrect date/time relative to where we are (I think it's UTC). I use a function in all my code on that db instead of GetDate() that uses this, which I found somewhere:

DATEADD(minute, DATEDIFF(minute, '20120101', CAST(GETDATE() AT TIME ZONE 'UTC' AT TIME ZONE 'Pacific Standard Time' as datetime)), '20120101')

I'd be interested to see if there are better ways, but this works for me...as long as I remember to use it instead of GetDate().