QUESTION::PIPELINE::COPY DATA::MAPPING::DATETIMEOFFSET FORMAT

Last OPDG release was a week ago, so a week after You found this bug. But still no solution for this? In my case, all dates and times are in UTC and time zone is +2, so i get all results with -2 hours. Date fields with T00:00:00 are converted to -1 day T22:00:00....

dbrowne I see... are you implying it is the OPGW that is doing the conversion and not the Copy data activity?

The problem is that I have a Copy data activity loading from an on-prem DB using this SQL query:

SELECT 
		[Date]
	,	Refresh_Date  <-- this is converted to UTC when it should not be
	,	ID
	,	Display
	,	Layout
	,	MUP_screenshot
FROM 
	vMUP_Status_15M

How do you suggest I use a function in the SQL query?  I don't think it's possible.  I would need to intercept every row that comes back from the source, basically iterate over the result set that Copy data receives back before it loads it into the destination, ie the LH.

I am not aware that Copy data let's you do this.  And quite frankly, having to spin up a Notebook to do this is extra complication and extra CU consumed and that is not acceptable since the Copy data activity has a Mapping setting for DateTimeOffset format.

So what is the proper way of using this DateTimeOffset format setting?

So here is the test result: first, the Copy data activity Source setup:

Then the result of running this SQL on the on-prem server in the LH:

As you can see, the time part has been shifted 4 hours into the future for some reason.  Under the Mappping tab, all options are empty.  So something is happening under the hood, is it not?

Are you saying that if my DATETIME2 column as date and time in EST, then it will be copied as such, with no changes, by Copy data?

I forgot to address your comment re the view doing something strange or my DB tool (DBeaver) misleading me.  One thing that needs to be understood, all that comes into DB already in EST.  So it is not a case where DBeaver grabs this data and converts it from UTC to EST when it displays the result set from some SELECT command I issue.  This is the case for all our DBs which data are then used in PBI reports and show up as EST without any explicit conversion.  

Further, the DDL for the view re the case at hand is as follows:

CREATE   VIEW vMUP_Status 
AS
	SELECT  
		CAST(lastAccessed AS DATE)		AS [Date]
	,	lastAccessed					AS Refresh_Date
	,	statusId						AS ID
	,	display							AS Display
	,	currentLayout					AS Layout
	,	CAST('' AS XML).value(
							'xs:base64Binary(xs:hexBinary(sql:column("MUP_screenshot")))'
						,	'VARCHAR(MAX)'
						)				AS MUP_screenshot 

	FROM dbo.MUP_Status 
	WHERE 
		statusId		IS NOT NULL		AND 
		MUP_screenshot	IS NOT NULL;

So no timezone offset conversion or anything. We grab lastAccessed (type DATETIME2) as it was stored, namely in EST. 

This is why I can only conclude it is something on the backend of Copy data activity that is converting the original data from EST to UTC.  Besides, why would the Copy data activity have a Mapping-->DateTimeOffset format setting?

I just wish someone would tell me why this setting is doing nothing all.  Am I specifying the string wrong? (shown in prev posts)