Convert to UTC

Question

Convert to UTC

Nagesh CL 701

Hi All,
I have a sql table which contains the Date and the Timezone as below: -

Is there a way in SQL to convert the OrderDate to UTC timezone based on the TimeZone field?

Thanks.

Regards,
Nagesh CL

Answer accepted by question author

1 additional answer

Your answer

Answer 1

Alberto Morillo 35,406 MVP Volunteer Moderator

Here is another function that you can provide the 2 pieces on information you have, Date and Time zone.

Answer 2

Alberto Morillo 35,406 MVP Volunteer Moderator

Please see the recommended options like AT TIME ZONE on this article.

Here you will find more examples of how to use AT TIME ZONE.

You may want to use a function like the one below:

CREATE FUNCTION [dbo].[fnGetLocalDate] (@DateToConvert DATETIMEOFFSET = NULL)   
RETURNS TABLE   
AS   
    RETURN   
      SELECT CONVERT(DATETIMEOFFSET, @DateToConvert AT TIME ZONE   
                                     (SELECT Value   
                                      FROM   LookUp.Config   
                                      WHERE  Property = 'TimeZone')) AS   
                    LocalDate

Below an example of how to use it.

SELECT o.name, L.LocalDate  
FROM sys.objects o  
CROSS APPLY [dbo].[fnGetLocalDate](o.modify_date) AS L

Nagesh CL 701 Reputation points

2021-08-25T08:12:48.317+00:00

Thanks for responding @Alberto Morillo

What i understand from the links is we have a sys (sys.time_zone_info) table which contains the Current_utc_OffSet value (for every timezone) that should be used in DateAdd function to calculate the equivalent UTC.

But the problem is, my date values (OrderDate) that i get can be in the past as well. I would need to convert to UTC based on the day light saving of that particular order date and time zone.

All the info i have is, OrderDate and the TimeZone of the OrderDate.

Share via

Convert to UTC

1 additional answer

Your answer