DateDiff and NOW do not work

[afinman]

I have an increasingly frustrated with PowerBI

I have many tables with DateTime values in from SQL Server. I want to do DateDiff functions on these in PowerBI comparing to NOW() so I can get Days/Hours/Minutes since these dates, but PowerBI just gets upset and errors:

I have had to resort to creating views in the database to do this which seems silly. I have been playing this morning and note that when I do StartedMinutesAgo = DATEDIFF(NOW(), ProbeRun[StartTime], MINUTE) it fails, but If I do StartedMinutesAgo = DATEDIFF("2017-01-01", ProbeRun[StartTime], MINUTE) it works fine which presumably means it is unhappy with the output of NOW() and TODAY() rather than the data in my database tables.

The error is

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.. The exception was raised by the IDataReader interface.

HELP!

[v-chuncz-msft · Accepted solution]

@afinman,

As shown in the error message, the data type is nvarchar and can not be automatically converted to datetime for the current date/time settings. You'll need to perform explicit conversion accordingly.

[afinman]

I have an increasingly frustrated with PowerBI

I have many tables with DateTime values in from SQL Server. I want to do DateDiff functions on these in PowerBI comparing to NOW() so I can get Days/Hours/Minutes since these dates, but PowerBI just gets upset and errors:

I have had to resort to creating views in the database to do this which seems silly. I have been playing this morning and note that when I do StartedMinutesAgo = DATEDIFF(NOW(), ProbeRun[StartTime], MINUTE) it fails, but If I do StartedMinutesAgo = DATEDIFF("2017-01-01", ProbeRun[StartTime], MINUTE) it works fine which presumably means it is unhappy with the output of NOW() and TODAY() rather than the data in my database tables.

The error is

The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.. The exception was raised by the IDataReader interface.

HELP!

[afinman]

I have just found that the relative search for dates/times errors with the same issue.

Am wonderinf if this is a locale issue with PowerBI and SQL...