REST API JSON DATE FORMAT

Question

I am trying to copy data from rest api source using the azure copy activity. I have used Rest Api as source and AZ SQL DB as target. But the json response I am receiving is having the date as below format: {Createddate: /date(345667999)/}

But when I hit preview data its giving the correct date format.(yyyy-mm-dd).

Can anyone suggest why is it like this? Is there any workaround to get the date value with correct format in the copy activity itself?

Answer 1

Hello @Mark de Jonge ,

Welcome to the Q&A .

Do you know as to what is the format of the datetime being exposed by the API ? I am guessing that its epoch time , if thats the case then you can

• ADF expression .
  You can use an expression like @addSeconds(' 1970-01-01', int(pipeline().parameters.timeelapsedinseconds))
  The below image will help you understand the implemenation .
  
• Since you have SQL as the sink , you can also use the below query .

    SELECT DATEADD(s, 1595284183, '19700101')   
  

  2020-07-20 22:29:43.000

Let me know how it goes and if you have any further queries .

Thanks & stay safe

Himanshu

Please do consider to click on "Accept Answer" and "Up-vote" on the post that helps you, as it can be beneficial to other community members