Forum Discussion
QUESTION::PIPELINE::COPY DATA::MAPPING::DATETIMEOFFSET FORMAT
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?
Hi Element115 ,
Thanks for using Fabric Community.
At this time, we are reaching out to the internal team to get some help on this .
We will update you once we hear back from them.
Hi Element115 ,
Can you please refer this - sql - Convert Datetime column from UTC to local time in select statement - Stack OverflowYeah 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?
- 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"?
