Convert int data type to Date format

[Hopeisgood1]

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

[danextian]

Hi @Hopeisgood1 

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]))

[Hopeisgood1]

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',

[danextian]

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.

[Hopeisgood1]

@danextian:

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

[Hopeisgood1]

@danextian It fired the Import Mode message 😞

[Hopeisgood1]

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 🙂

[danextian]

Try this:

Date.From(#datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, Number.From([Submit_Date])))

[Hopeisgood1 · Accepted solution]

@danextian 

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

[Deku]

Would only force import mode if it doesn't fold in powerquery to be incorporated in the SQL query send to the database