In WebTMA 7, 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 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.
You can also use the Convert() function with dbo.fun_DateTimeUTCtoLocal and dbo.fun_GetTimeZoneID to format the request date. Please see the example below for converting UTC to Local time.