[Imported] Convert Local Time to UTC

[sbeadle]

>>Message imported from previous forum - Category:Scripts and Tips<<
User: mchartrand, originally posted: 2018-10-23 17:18:01 Id:176
This is a re-posting from the obsoleted (October 2018) "Schneider Electric Telemetry & SCADA" forum.

_________________

**_NIWTelemetry:
Hi
I am using the following script to populate a data table (user name, date and time) every time a user navigates to a mimic._**

_Sub Mimic_Load()
Dim SqlResult
Dim User
Dim SQL
Dim CurrentDTString
User = Server.UserName
CurrentDTString = CStr(DatePart("yyyy",Date))+"-"+Right("0"&DatePart("m",Date), 2)+"-"+Right("0"&DatePart("d",Date), 2)+" "+CStr(Time)
SQL = "INSERT INTO UseageTable VALUES ('"+ User +"',"+"timestamp'"+ CurrentDTString +"')"
Set SqlResult = Server.Query(SQL)
If SqlResult.Error Then
End If
End Sub_

**_Problem is everything works as expected but the time is always one hour ahead of local time. I need to convert Time to Local time and looking for some help please. Thanks._**

___________

hardin4019:
Hey Tony,

Pull up the help menu in CS and search "Script UTC to Local" and there is an example of how to change to local time you can put into an internal variable inside your script, then use that internal variable for the rest of your script.

___________

**_NIWTelemetry:
Hi Shawn
Thank you for the update. I made a slight error - I actually need to convert local time to UTC.
I have tried to use the 'LocalTimeToUTC' as below - but does not appear to work. Do you have any suggestions? Thanks._**

_Sub Mimic_Load()
Dim SqlResult
Dim User
Dim SQL
Dim CurrentDTString
User = Server.UserName
CurrentDTString = CStr(DatePart("yyyy",Date))+"-"+Right("0"&DatePart("m",Date), 2)+"-"+Right("0"&DatePart("d",Date), 2)+" "+CStr(LocalTimeToUTC(Time))
SQL = "INSERT INTO UseageTable VALUES ('"+ User +"',"+"timestamp'"+ CurrentDTString +"')"
Set SqlResult = Server.Query(SQL)
If SqlResult.Error Then
End If
End Sub_

___________________

bevanweiss:
Is there a reason that you're storing the DateTime as a string?
This would be considered bad database practise. The correct datatype for a DateTime value is of course a DateTime datatype... of which ClearSCADA datatables support this.
You should then be able to use an SQL builtin GETUTCTIME (I actually can't remember the name of the function...) to do the INSERT stuff.

_________________________

**_NIWTelemetry:
Hi
The DateTime is being picked up in a Crystal Report and I need it to be stored as a string for another purpose.
Interesting comment about GETUTCTIME - I will look into this. Thank you for your reply._**

_________________________

bevanweiss:
You can format a DateTime column as you like in Crystal Reports, no need to store it in the table as a string for that.
And your other purpose should be able to handle it as a DateTime format field also, best stored in UTC also (no Daylight Saving Time shifts).

GETUTCTIME was wrong, it should be {OPC 'NOW'}..

______________________

**_NIWTelemetry:
I've changed the code to use: LocalTimeToUTC(NOW) and its working just as i want. Thank you for your comments._**