Forum Discussion
DataSource.Error: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time or timestamp value.
Hello everyone,
I'm using the ODBC connector to get data from Snowflake into PowerBI. I'm now getting the following error (DataSource.Error: ERROR [22007] [Microsoft][Support] (40490) Invalid date, time, or timestamp value), but everything is good in the database. This error is for all the tables in my database. How can I solve it and prevent it from happening again?
Thanks
3 Replies
Hi, julsr
Based on your description, you can try the following method to solve the problem.1. Check and Adjust Date/Time Formats, ensure that the date, time, or timestamp columns in your Snowflake database are in a format that the ODBC connector can correctly interpret. You may need to adjust the query you're using in Power BI to cast these columns to the appropriate format.
2. Update ODBC Driver, make sure you are using the latest version of the ODBC driver for Snowflake. An outdated driver might not handle date, time, or timestamp values correctly.
Snowflake ODBC Driver 32/64-bit: Download connector for integration and sync (devart.com)
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
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.1558Just 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')
