I have developed 2 SQL functions for this:

create function utc_to_local_datetime
(
 @utc_date_time date_time ,
 @time_zone_id time_zone 
)
returns date_time
as
begin


 DECLARE @offset_minutes AS INT

 SET @time_zone_id = COALESCE(@time_zone_id, dbo.get_user_setting('time_zone_id', NULL), 'Central European Standard Time')

 SET @offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @utc_date_time) AT TIME ZONE @time_zone_id, 6)) x)

 RETURN DATEADD(MINUTE, @offset_minutes, @utc_date_time)


end
go

grant execute on utc_to_local_datetime to public
go

create function local_to_utc_datetime
(
 @date_time date_time ,
 @time_zone_id time_zone 
)
returns date_time
as
begin

 DECLARE @raw_offset_minutes AS INT

 SET @raw_offset_minutes = (SELECT CAST((LEFT(offset, 1) + '1') AS INT) * DATEDIFF(MINUTE, '1/1/1900', CAST(right(offset, 5) AS DATETIME)) FROM (SELECT offset = RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 6)) x)

 RETURN DATEADD(MINUTE, -1 * @raw_offset_minutes, @date_time)

 -- OTHER SOLUTION (NOT WORKING WITH NEGATIVE OFFSET)
 -- RETURN CONVERT(DATETIME, @date_time) - CONVERT(CONVERT(TIME, RIGHT(CONVERT(DATETIME, @date_time) AT TIME ZONE @time_zone_id, 5)))


end
go

They work fine, but it is still hard to let them work for al scenarios. For example a user in Japan, who wants to schedule a task in Brazil 6 months in advance.

A local time control would be very welcome. Solving the problem with SQL is possible, but sometimes has serious impact on performance.

Hi Andre,

In the future we want to have timezone conversion transparently handled by Indicium, but until then you will indeed have to do the conversion in your business logic yourself. We don't have a schedule for this yet. 

You could save all datetimes in UTC and use the SQL timezone table (sys.time_zone_info) to show the UTC time in users registered timezone  using SWITCHOFFSET() and the UTC offset in the system table.