Forum Discussion
Need help with transforming the Date
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]
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
1 Reply
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