Forum Discussion

michellepace's avatar
michellepace
Icon for Resolver III rankResolver III
6 years ago
Solved

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

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

Need Help
  • michellepace's avatar
    michellepace
    6 years ago

    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

5 Replies

    • michellepace's avatar
      michellepace
      Icon for Resolver III rankResolver III
      6 years ago

      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's avatar
        ImkeF
        Icon for Community Champion rankCommunity Champion
        6 years ago

        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)

         

    • michellepace's avatar
      michellepace
      Icon for Resolver III rankResolver III
      6 years ago

      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