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

[Element115]

QUESTION:

Does anyone know how to specify correctly the DateTimeOffset format of Copy data activity?

ISSUE:

The data source is on EST time and the Copy data activity should load the data into a LH with all timestamps in EST timezone and not UTC.  Therefore I changed the Mapping settings of the Copy data activity like so:

DateTimeOffset format --> yyyy-MM-dd HH:mm:ss-04:00

But it didn't do anything.  All timestamps loaded into the LH are still in UTC timezone.

Am I missing "" marks around the yyyy-MM-dd HH:mm:ss-04:00 string?  And if not, is it a bug or is there another syntax to use for this?

And by the way, having a help link redirect to a .NET documention page for this is not really helpful because it still does not tell you whether or not the settings field in the Copy data activity expects the string to be surrounded by single or double quote marks.  Tiral and error takes precious time away from the business task at hand.

[Element115]

Yeah I know how to do that.  That's not what I am talking about.  The Copy data activity is what converts my datetime from EST to UTC.  So the output from Copy data is in UTC and this output is a result set.  How do you suggest I convert the datetimes in that output back to EST??  Certainly not by converting in T-SQL at the source from EST to UTC, right?

There is a bunch of settings called Mapping for the Copy data activity.  One of which is:

DateTimeOffset format --> yyyy-MM-dd HH:mm:ss-04:00

Why does the above not do the trick, that's the question?

[dbrowne]

What do you mean by "The Copy data activity is what converts my datetime from EST to UTC."? What is the data type of the source and destination columns? And what is a sample value that is being "converted"?

[Element115]

What I mean is this:

Q0__What do you mean by "The Copy data activity is what converts my datetime from EST to UTC."?

A0__The source datetime is in EST.  The result set output by Copy data is in UTC, ie it does not preserve the source datetime as EST.

Q1.1__What is the data type of the source and destination columns?

A1.1__DATETIME2.  This script is used to in the Copy data Source tab setting:

SELECT 
		[Date]
	,	Refresh_Date
	,	ID
	,	Display
	,	Layout
	,	MUP_screenshot
FROM 
	vMUP_Status

Column Refresh_Date is of type DATETIME2 and contains time in EST:

Q1.2__...and destination columns?

A1.2__Destination is a lakehouse, so the table and columns are created automatically the first time the pipeline runs.  The schema for this Refresh_Date column in the LH is thus:

So also DATETIME2.

Q2__And what is a sample value that is being "converted"?

A2__Look at the Refresh_Date values in the following result set run from the LH SQL endpoint using this SQL script:

SELECT [Date], Refresh_Date
FROM MUP_Status
WHERE [Date] = '2021-04-30'
ORDER BY Refresh_Date DESC
;

Compare this result set to the one obtained at the source with the same SQL script:

The values coming out of the LH are later in time as if offset +4 as in UTC time. This is how they also show up when accessed from a DF. Hence they require manual conversion either in Power Query with M or in the report with DAX.

So I ask, what is the use of the Mapping-->DateTimeOffset format in the Copy data settings?
 

Using information from this page of the .NET documention, the above DateTimeOffset format string is composed: 
Custom date and time format strings - .NET | Microsoft Learn

So how do we get DATETIME2 column types to load into a LH without being changed to UTC or any other timezone?