Hi, am struggling to overcome error on PowerBI desktop refresh, latest 2.59.5135.421 64-bit (June 2018):
Error Message:
Invalid date/time: 2016-05-24
Looks trivial, but difficult to pinpoint, as the date here looks fine internally (generic US/SQL format YYYY-MM-DD) although app set to English UK Regional Settings and connected through Import Mode to same region SQL Server View (which shouldn't be a problem).
All other dates from SQL Views are fine, apart from the source SQL DateTime datatypes causing this. They link (via USERELATIONSHIP because there are multiple date joins) to a SQL Date table also imported into PowerBI so I set them as Date datetype in both the Modelling tab and the Import Query (when the former didn't do the trick) - otherwise only midnight 00:00:0000 time records join properly.
As DBA, I can change the format in SQL Server fine, although ideally want to keep the output with time and only convert it to Date only in PowerBI (especially as SSMS designer creates errors when applying functions to aggregated dates etc). The error occurs when
CONVERT(DATE, <date column>) AS TheDate
is applied in SQL Server (doesn't matter if I leave as default or 103 for British format etc) - but if I don't do that, they will come through as DateTimes, which even switching to Date in PowerBI as above, won't join properly. So a trap. I can possibly make PowerBI work by only importing a created SQL View of a View of the Table, but that is messy and should be unnecessary - best to get to bottom of this PowerBI error for a clean solution.
So the dates being fed currently are indeed in format (SQL View output in SSMS): "2016-05-24" so I can't see the problem, as all the other dates in that format do NOT create an error (only these DateTimes converted to Date)!
Would appreciate any heads up as to why or how to verify where internal format PowerBI is actually failing on with this date format error message (which seems odd in itself), and how we can resolve it going forward - ie is there a better type of date format from SQL View to feed it to avoid such problems?
TIA,
Phil
