Forum Discussion
Convert int data type to Date format
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
It is a good practice to inform everyone beforehand what storage mode you're using.
That aside, try this:
#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.From([Submit_Date]))
I tested this on SQL Server so this might not work with other flavors.