How to add timezone to the datetime column when using Salesforce destination component?

How to add timezone to the datetime column?

When using Salesforce destination component, the timezone will not be added if the input time doesn't have any time zone indicator. To add the timezone there are 2 solutions:

1)  Append the timezone offset prior to sending the data to Salesforce.

2) Store the timezone offset with the datetime.

--------------------------------------------------------------------------------------------------------------------------

-- Here is an example using SQL Server (see attached screenshot)
--------------------------------------------------------------------------------------------------------------------------

CREATE TABLE dbo.Src_DateTime
    (  
    ColDatetimeoffset datetimeoffset  
    );  
GO  

--Returns: 2019-09-26 09:59:34.260
--Returns(UTC): 2019-09-26 13:59:34.260
SELECT GETDATE() AS Current_DateTime
      ,GETUTCDATE() Current_UTC_DateTime
GO

--Returns: 2019-09-26 09:59:34.2600000 -04:00
DECLARE @CurrentDateTimeUTC datetime2 = '2019-09-26 13:59:34.260'
SELECT @CurrentDateTimeUTC AT TIME ZONE 'UTC'
                           AT TIME ZONE 'Eastern Standard Time'
GO

SELECT DateDiff(minute, GetUTCDate(), GetDate()) OffSet
GO

--Add timezone offset with DateTime 
INSERT INTO dbo.Src_DateTime   
VALUES ('2019-09-26 09:59:34.260 -4:00');  
GO

--Returns: 2019-09-26 09:59:34.2600000 -04:00
SELECT ColDatetimeoffset  
FROM dbo.Src_DateTime;  
GO

--Returns: 2019-09-26 13:59:34.2600000 +00:00    
SELECT SWITCHOFFSET (ColDatetimeoffset, '+00:00')   
FROM dbo.Src_DateTime;  
GO


Screenshot:



    Book a Presales Call


    Are you new to COZYROC and evaluating our solutions? 
    Book a Presales Call with us now and get 5% off SSIS+.