Forum Discussion
Convert int data type to Date format
Hi I have a direct query code from MS SQL in my Power BI desktop and I am trying to convert these two columns into date format (WITHOUT the time stamp).
Thank you,
Holly
I figured it out...Since the UNIX timestamp we have is an OLD UNIX, the data type is INT, not BIGINT. The BIGINT is for the newer version of UNIX. I am learning all about this UNIX Timestamp that is also called Epoch. Just wow!
This is the code -
SELECT work_order_ID, Submit_Date,Convert(varchar,DATEADD(MILLISECOND, CAST(RIGHT(Submit_Date, 3) AS INT)
- DATEDIFF(MILLISECOND,GETDATE(),GETUTCDATE()), DATEADD(SECOND, CAST(LEFT(Submit_Date, 10) AS INT), '1970-01-01')),110)as 'PBI Submit Date'
from dbo.WOI_WorkOrder
where DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')) >= '01/01/2024'In SQL Server
In Power BI from Direct Query
9 Replies
Hi Anonymous
It looks like a UNIX time stamp which represents the number of seconds from Jan 1, 1970 UTC. Try this custom column formula in the query editor:
DateTime.From(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [Submit_Date]))Would only force import mode if it doesn't fold in powerquery to be incorporated in the SQL query send to the database
