Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Hopeisgood1
Resolver I
Resolver I

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

 

Hopeisgood1_0-1741818823626.png

Thank you,

Holly

 

 

1 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

 

Hopeisgood1_0-1741899355157.png

 

In Power BI from Direct Query

 

Hopeisgood1_1-1741899520764.png

 

 

View solution in original post

9 REPLIES 9
danextian
Super User
Super User

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

danextian_0-1741821175245.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

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

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

 

danextian_0-1741835628083.png

danextian_1-1741835645851.png

I tested this on SQL Server so this might not work with other flavors.





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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

 

@danextian It fired the Import Mode message 😞

 

Hopeisgood1_0-1741887142431.png

 

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 🙂

Try this:

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

danextian_0-1741852692284.png

 





Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!


"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

@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

 

Hopeisgood1_0-1741899355157.png

 

In Power BI from Direct Query

 

Hopeisgood1_1-1741899520764.png

 

 

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


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.