Forum Discussion

Element115's avatar
Element115
Memorable Member
2 years ago

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

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...
data engineering
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Element115 ,

Thanks for using Fabric Community.

Did you tried with the date functions? It is very easy to do convertions.

@convertFromUtc(utcnow() ,'Eastern Standard Time')





Docs to refer - Expressions and functions - Microsoft Fabric | Microsoft Learn

If you have any format issues, you can also refer this thread - Link


Hope this is helpful. Please let me know incase of further queries.

  • Element115's avatar
    Element115
    Memorable Member
    2 years ago

    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?