Convert Date to UTC in SQL Server 2014

Question

Convert Date to UTC in SQL Server 2014

Navigation

#1 by (3 votes)

2

Hello I have a record of calls made from different countries, I have the date and the country, the dates are adjusted to the time zone of each country, but I need to convert it to UTC, since I need to calculate the number of calls made in a month for all countries.

What they asked me to do is convert all the dates to UTC and take that date to do the calculation.

How can I do it? Any ideas?

I'm using this

PRINT DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), [fecha_llamada])

But that transforms it from the server's local time zone to UTC, but each date has a different time zone.

Try the same with the following function, but it is the same problem, it transforms me as if everything were from the same time zone.

CREATE FUNCTION dbo.fn_ConvertToDateTime (@Datetime BIGINT)
RETURNS DATETIME
AS
BEGIN
    DECLARE @LocalTimeOffset BIGINT
           ,@AdjustedLocalDatetime BIGINT;
    SET @LocalTimeOffset = DATEDIFF(second,GETDATE(),GETUTCDATE())
    SET @AdjustedLocalDatetime = @Datetime - @LocalTimeOffset
    RETURN (SELECT DATEADD(second,@AdjustedLocalDatetime, CAST('1970-01-01 00:00:00' AS datetime)))
END;
GO

Update 12/14/2018 # 1

Attached structure and example of the table

CREATE TABLE [TMP].[PortaCallDetails](
    [ID] [int] NULL,    
    [charged_amount] [decimal](18, 3) NULL,
    [charged_quantity] [varchar](50) NULL,
    [country] [varchar](50) NULL,       
    [connect_time] [varchar](50) NULL,
    [unix_connect_time] [varchar](50) NULL,
    [disconnect_time] [varchar](50) NULL,
    [unix_disconnect_time] [varchar](50) NULL   
) ON [PRIMARY]

The data in the table are in text because they are taken from a file, this table is a temporary one from which the data for the definitive table is taken and transformed.

Update 12/14/2018 # 2

I need a function like this:

PRINT getUtcDate(@Date, @OffSet)

or with the country

PRINT getUtcDate(@Date, @Country)

sql sql-server date offset

asked by SOSkr 13.12.2018 в 17:58

source

1 answer

Restart Mysql before the maximum connections arrive Form of symfony, field of type FileType

score 3 · Accepted Answer

Based on everything I read and considering that you can make changes to your handover table, the query should look like this:

SELECT 
  P.*, 
  DATEADD(hh,T.TimeDifference*-1,P.connect_time) [LocalTime]
FROM PortaCallDetails P
INNER JOIN TimeZone T ON P.TimeZone_id=T.TimeZone_id

With connect_time of type DateTime previously transformed with SSIS and TimeZone_id the numeric id of the time zone to improve the comparison of types with an index in this field in both tables

and the TimeZone table should be

CREATE TABLE TimeZone(
  TimeZone_id smallint,
  country VARCHAR(50),
  TimeDifference smallint
)

Since you have the TimeZone table just add the difference of hours (* -1) to revert the local time to the UTC time.

An alternative solution with the same calculation but thinking about optimizing the code for reuse we can create a function

Function fn_LocaltimeToUTC

CREATE FUNCTION dbo.fn_LocaltimeToUTC (@LocalDatetime DateTime, @TimeZone_Id Smallint)
RETURNS DATETIME
AS
BEGIN
    RETURN DATEADD(hh,(
        SELECT TimeDifference*-1 FROM TimeZone WHERE TimeZone_Id=@TimeZone_Id
    ),@LocalDatetime)
END;
GO

Being the query more readable and being able to leave the function to reuse in other queries

SELECT 
  P.*, 
  dbo.fn_LocaltimeToUTC(P.connect_time,P.Timezone_id) [LocalTime]
FROM PortaCallDetails P