Forum Discussion
Continued ContributorDataSource.Error: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time or timestamp value.
I have a similar issue, but can see that the Query sent from Power BI to Snowflake has an error in the definition of the timestamp. It simply uses '.' instead of ':' for two of the three timestamp separators.
Example of query is below.
SELECT "DIMABSENCETYPEKEY",
...
"RECORDSHOPSALESAGGDKEY"
from "zzz_DEV"."REPORTING"."zzz"
where "ROW_INSERT_DT" >= TO_TIMESTAMP('2015-01-01 00.00.00.0000000','YYYY-MM-DD HH24:MI:SS.FF9') and "ROW_INSERT_DT" < TO_TIMESTAMP('2030-01-01 00.00.00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')
For me this is not possible to change but something that must be configured by Power BI/Microsoft.
Power BI desktop version 2.141.1558
Just tested if this would be fixed by switching to the new Snowflake connector. This did not solve the issue.
Power BI still produces a query using '.' instead of ':' from Power BI Desktop.
Where statement for filter on datetime value.
where "zz" >= TO_TIMESTAMP('2015-01-01 00.00.00.0000000','YYYY-MM-DD HH24:MI:SS.FF9') and "zz" < TO_TIMESTAMP('2030-01-01 00.00.00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')
This should be:
where "zz" >= TO_TIMESTAMP('2015-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9') and "zz" < TO_TIMESTAMP('2030-01-01 00:00:00.0000000','YYYY-MM-DD HH24:MI:SS.FF9')