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

[michellepace]

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

[v-eachen-msft]

Hi @michellepace ,

Try to add a sql query with FORMAT() and import data as text. 

[michellepace · Accepted solution]

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

[ImkeF]

Hi @michellepace ,

try disabling the automatic type detection in the options:

[michellepace]

Hi @ImkeF  thank you for your speedy reply and effort to make such an easily understantable screenshot. I tried as you suggested however I have the same error as originally posted. 🙁

[ImkeF]

Hi @michellepace ,

can you copy and paste the code that has been generated for this query? (Delete any private data of the actuals datasource, but keep all M-function in please)