In WebTMA 5, the Date/time conversion from UTC to Local time is straightforward. It entails using two SQL functions:
- dbo.fun_DateTimeUTCtoLocal
- dbo.fun_GetTimeZoneID
dbo.fun_DateTimeUTCtoLocal is used because WebTMA stores all date/time values in UTC to support multi-time-zone deployments. This function converts a UTC datetime into local time by:
- Applying the correct time zone offset.
- Accounting for Daylight Saving Time (DST).
- Ensuring consistency with the dates shown in the WebTMA.
- dbo.fun_GetTimeZoneID returns the appropriate time zone ID for date/time conversions.
Using @userPK allows the query to retrieve the user's time zone preferences from their WebTMA account.
Use @userPK and your WebTMA Client PK (i.e. 1000) in dbo.fun_getTimeZoneID so that it pulls in the time zone preference for the logged in user. Put this inside the dbo.fun_DateTimeUTCToLocal so that the function can take the user's current time (UTC) and convert it to the user's time zone preference. Please see the example below for converting UTC to Local time.