ACCESS equivalent to SQL Server "GETUTCDATE()" ? 1

[4946]

I need to be able to identify the local machine's time offset from GMT within an ACCESS query. The SQL Server "GETUTCDATE()" provides that ability using the same query code regardless of whether it is Standard time or Daylight Saving Time.

select datediff(hh, '01/01/1970', getdate()) - datediff(hh, '01/01/1970', getutcdate())

Does anyone have any suggestions or solutions for the same in ACCESS/JET SQL or VBA ?

Thanks in advance.

WinN

 

[flyover789]

the following function returns what you want (I didn't develop it myself, but unfortunately don't remember where I found it nor who deserves the credit for it):

Function timezonebias() As Double
[green]'determine the time difference of the system time to GMT[/green]
Dim objShell, lngbiaskey, lngbias, k
Set objShell = CreateObject("Wscript.Shell")
lngbiaskey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
  & "TimeZoneInformation\Bias")
If UCase(TypeName(lngbiaskey)) = "LONG" Then
  lngbias = lngbiaskey
ElseIf UCase(TypeName(lngbiaskey)) = "VARIANT()" Then
  lngbias = 0
  For k = 0 To UBound(lngbiaskey)
    lngbias = lngbias + (lngbiaskey(k) * 256 ^ k)
  Next
End If

[green]'lngbias = difference in minutes[/green]
timezonebias = lngbias / 60 / 24 [green]'timezonebias = difference in days[/green]
End Function

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Shared Database_Systems and _Applications across all Business_Areas[/blue]

 

[4946]

HTH,

Thank you for your quick reply. I will be using your code today and will get back in touch.

WinN

 

[4946]

fly,
Thanks again for the code!! In XP PRO there is an "ActiveTimeBias" value in the registry that appears to change with the Standard/Daylight Saving time shifts that I will use in hopes that this code will always show the current GMT/Local offset. Also made a change to show the offset time in hours. (Mods have been shown in red)

Function timezonebias() As Double
[COLOR=green]'determine the time difference of the system time to GMT[/color]
Dim objShell, lngbiaskey, lngbias, k
Set objShell = CreateObject("Wscript.Shell")
lngbiaskey = objShell.RegRead("HKLM\System\CurrentControlSet\Control\" _
  & "TimeZoneInformation\[COLOR=red]ActiveTime[/color]Bias")
If UCase(TypeName(lngbiaskey)) = "LONG" Then
  lngbias = lngbiaskey
ElseIf UCase(TypeName(lngbiaskey)) = "VARIANT()" Then
  lngbias = 0
  For k = 0 To UBound(lngbiaskey)
    lngbias = lngbias + (lngbiaskey(k) * 256 ^ k)
  Next
End If

[COLOR=green]'lngbias = difference in minutes[/color]
[COLOR=red]timezonebias = lngbias / 60 [/color][COLOR=green]'timezonebias = difference in hours[/color]

End Function

Thanks again for your help and have a star!!

WinN