Forum Discussion
QUESTION::PIPELINE::COPY DATA::MAPPING::DATETIMEOFFSET FORMAT
A `datetime2` has no timezone information in it. Clients might _interpret_ it as being a local time in some timezone, but the Copy Data task will copy the value directy to a timetamp, which also has no timezone information.
Test by loading something like this:
I suspect whatever tool you're using to examine the data is misleading you, or the view is doing something strange.
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)