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.
Will you please put the SQL code in here as the Native Query you inserted cut off part of the code. I so appreicate the help you have been for me! 🙂
Holly