Convert UTC Fields to Local Time

When querying the ConnectWise database, sometimes the only date field you’ll have available is UTC meaning the time returned is not user-aware and you’ll need to adjust it. Or, you may want a report to show standardized time, no matter what timezone the end user is in.

To calculate the desired local time from a UTC field in Report Writer, you’ll input the SQL code in the Expression box of the Advanced Properties on the applicable field.

To default to the company timezone set in Manage, you’ll enter the following code, replaced time_field_name_utc with your field and keeping the brackets:

udf_GetLocalTimeFromUTC([time_field_name_utc], 1, '')

There are a few other options available. To use a Manage member’s default time, you can replace the “1” with “3” and the ” with ‘member_id’:

udf_GetLocalTimeFromUTC(getdate(), 3, 'MemberID') 

Or, choose a timezone from the time_zone_name table to convert UTC to:

udf_GetLocalTimeFromUTC(getdate(), 2, 'Pacific Standard Time')

You can read more about the function at the ConnectWise University documentation page (login required).

Now you know how to convert UTC fields in ConnectWise Manage Report Writer. Contact us for one-on-one help or connect with us on the ConnectWise subreddit or the ConnectWise University Forums.