Need help with transforming the Date

[Anonymous]

I have a few date fields that are coming from sql server and the data is retreived as a number and i tried to transform it to date but it is not working. 

I need help on how to transform this number to date. I have data coming from more than 10 tables and i dont want to use a direct SQL query as i have to face a different issue when i have to refresh the data in the future. 

when i ran the below query in SQL i got the dates correctly as they should.

SELECT [kee]
      ,CAST(DATEADD(SECOND, [created_at]/1000,'1970/1/1') AS DATE) createdDate
      ,CAST(DATEADD(SECOND, [updated_at]/1000,'1970/1/1') AS DATE) updateDate
      ,CAST(DATEADD(SECOND, [issue_creation_date]/1000,'1970/1/1') AS DATE) issueCreatedDate
      ,CAST(DATEADD(SECOND, [issue_update_date]/1000,'1970/1/1') AS DATE) issueUpdatedDate
      ,CAST(DATEADD(SECOND, [issue_close_date]/1000,'1970/1/1') AS DATE) issueClosedDate
FROM [XXXXXX].[dbo].[XXXXXX]

[Anonymous · Accepted solution]

HI @Anonymous,

Here is the expression that you can use in query editor to add a new column convert from timestamp to datetime: (you only need to repalce the field to your timestamp field names)

 #datetime(1970,1,1,0,0,0) + #duration(0,0,0,[Field]/1000)

In addition, you can also try to use t-SQL query to replace values when you use the data connector that allow you to use SQL statement.

Regards,

Xiaoxin Sheng