Forum Discussion
DateDiff and NOW do not work
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!
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.
3 Replies
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!
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...
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.
