Forum Discussion

Anonymous

Not applicable

6 years ago

Power bi reading date incorrectly from msSQL table/Wrong date column

Hi all, I have been trying to import data directly from MSSQL to the query editor, however, the date/time columns are showing strange numbers (i.e. 1.45985E+12) and when I try to change the data ...

Tips and Tricks

Most Valuable Professional

6 years ago

Hi Anonymous,

which application does the table belong to?

Anonymous

Not applicable

6 years ago

I am using import mode to connect to msSQL tables for Manage Engine Service Desk Plus.

mwegener
Most Valuable Professional
6 years ago
Hi Anonymous ,

I think the program works with a UNIX timestamp.

Look at this.

https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
- Anonymous
  Not applicable
  6 years ago
  Hi mwegener,
  
  Please excuse my limited knowledge in SQL - I checked from the sql server and the data type is bigint however I have been trying since yesterday to add the below code in my advanced editor in the query editor but I keep getting errors.
  
  This is the code to add:
  
  Select dateadd(S, [unixtime], '1970-01-01') From [Table]
  
  And this is my original code from the server:
  
  let
  Source = Sql.Database("XXXXXX\MANAGEENGINE", "servicedesk"),
  dbo_WorkOrder = Source{[Schema="dbo",Item="WorkOrder"]}[Data]
  in
  dbo_WorkOrder
  
  Can you please advise?
  - tex628
    Community Champion
    6 years ago
    Try adding a custom column with something like this;
    
    Date.AddDays(#date(1970, 1, 1), [unixtime]/60/60/24)
    
    To my knowledge there is no dateadd which allows you to add seconds in powerquery... 😞
    
    Br,
    J

Related content