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
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]))
The problem with that - it would force me to use the Import MODE hence why I need to do it in the SQL code....
I have it the below coded in my SQL but I can't figure out to format it to "MM/DD/YYYY"
DATEADD("hh", -5, DATEADD("S", Submit_Date, 'January 1, 1970')) as '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
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.
It has the timestamp thou. Don't want that as it's already in the Power BI. Can you take out the timestamp and show me? And I will definitely try 🙂
