Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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).   Thank you, Holly    
Need Help
Show and Tell
  • Anonymous's avatar
    Anonymous
    1 year ago

    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

     

     

     

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
danextian
Super User
1 year ago

Try this:

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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