Hello,I am connecting to a Pervasive SQL database. When trying to connect to the SupplierMaster table I am getting the below date/time error in PowerBi. This is due to the "dirty" value I have in the LastCrDate date column, namely "11/30/0011".Question: What is the most elegant way to handle this "dirty date" scenario? (I'm not permitted to "fix" any values in the database) Thanks in advance, Michelle

Hi ImkeF .>can you copy and paste the code that has been generated for this query? It cannot be the code because once I fix the data in the datacase, everything works as it such. Thus ruling out the code. Nonethless, so you can see here it is: = Odbc.Query("dsn=PastelDB.odbc", "select LastCrDate from SupplierMaster") Hi v-eachen-msft ,> Try to add a sql query with FORMAT() and import data as text.Thank-you, this is a perfect answer! I changed my query to = Odbc.Query("dsn=PastelDB.odbc", "select convert(LastCrDate,SQL_VARCHAR) from SupplierMaster") . It works like a charm. Now I can handle what to do with dirty dates within Power Bi (eg convert a dirty date to 01/01/1970). I'll defintely use this in future scenarios where I cannot change the db. My Final Solution: The database owner gave me permission to "fix" dirty date values in his PastelDb. So I fixed them all with this by running the below sql against the pervasive database. Power Bi works fine now with the orginal purple query above. update "SupplierMaster" set LastCrDate = '1970-01-01' WHERE LastCrDate not between '1970-01-01' and '2020-07-29' Thank you both, I learnt something very useful in spite of the final solution.Michelle

Connection Error - how to elegantly handle dirty dates in a DATE column?

Forum Discussion

Related content

Connection failed using a Folder connection in a pipeline

Missing columns (API connection from Kobo)

connection to dataflows

Power BI Oracle Connection Error – ORA-50201 (Failed to connect / parse connect string)

Database connectivity.