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

[Anonymous]

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 format to Data/time or Time I get an error! 

Does anyone have an idea of things that I can apply that might help me? 

[mwegener]

Hi @Anonymous,

which application does the table belong to?

[Anonymous]

Hey @mwegener,

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

[mwegener]

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]

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]

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