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

Question

Hi all,&nbsp;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!&nbsp;&nbsp;Does anyone have an idea of things that I can apply that might help me?&nbsp;&nbsp;

mwegener · Answer

Hi&nbsp;Anonymous,
&nbsp;
which application does the table belong to?
&nbsp;
&nbsp;

anonymous · Answer

Hey&nbsp;mwegener,&nbsp;I am using import mode to connect to msSQL tables for Manage Engine Service Desk Plus.&nbsp;&nbsp;

mwegener · Answer

Hi&nbsp;Anonymous&nbsp;,
I think the program works with a UNIX timestamp.
&nbsp;
Look at this.
https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
&nbsp;
&nbsp;

anonymous · Answer

Hi&nbsp;mwegener,&nbsp;&nbsp;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.&nbsp;This is the code to add:&nbsp;Select dateadd(S, [unixtime], '1970-01-01') From [Table]&nbsp;And this is my original code from the server:&nbsp;letSource = Sql.Database("XXXXXX\MANAGEENGINE", "servicedesk"),dbo_WorkOrder = Source{[Schema="dbo",Item="WorkOrder"]}[Data]indbo_WorkOrder&nbsp;Can you please advise?&nbsp;&nbsp;&nbsp;

tex628 · Answer

Try adding a custom column with something like this;&nbsp;
&nbsp;
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
&nbsp;

Forum Discussion

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

5 Replies

Related content

Wrong Total in Table

PowerBI reading data format incorrectly

Power BI read aloud

Commitment value calculating incorrectly - DAX Logic

Gen2 dataflow read only mode issue