ODBC error 22007 with Pervasive SQL: Date/time data issue

[rbcbasic]

Hi All.

Hopefully someone can help me with a suitable solution / tip. I have successfully setup a 64bit ODBC connection to PSQLv13 which I intent to use with the Power BI Gateway. However, when I use Power Query to Get Data from the ODBC DSN, I get the following query with some of the tables within the Payroll PSQL database:

DataSource.Error: ODBC: ERROR [22007] [PSQL][ODBC Client Interface]Invalid date, time or timestamp value.
Details:
DataSourceKind=Odbc
DataSourcePath=dsn=[myDSNname]
OdbcErrors=[Table]

Now, I don't want to have to fix the source data (yes, there are some strange date values in some of the source tables), I don't want to have to run SQL statements to exclude them (Power Query is far quicker to select and remove columns in this case). Is there a way of ignoring the ODBC error, or forcing the field to come through as a text field, or something along thsoe lines? Any ideas?

[floressdiego]

I know this post is old, but I was having this same issue for over a year and couldn't find a solution until today.

I know this works when you IMPORT, I do not know if it works with Direct Query.

1 - Install the Maria ODBC Connector.

2 - Press Start, look for ODBC Data Sources in Administrative Tools.

3 - Add a new User DSN and input all the details needed. Should look something like this:

4 - Go to Power BI and Click Get Data. Select Blank Query. The function we will use is Odbc.Query where the first part is the connection string and the second one is the query. What I found out is that if you change the name and the type of the column inside that query you will stop getting the data type error. The table I had the error was "sd_atividade_chamado" and it was setting the data type before I could do anything. The query will be something like:

Odbc.Query(,"SELECT
CAST(cd_chamado AS CHAR) AS cd_chamado_texto,
CAST(cd_atividade_chamado AS CHAR) AS cd_atividade_chamado_texto,
CAST(cd_atendente AS CHAR) AS cd_atendente_texto,
CAST(cd_atendente_chamado AS CHAR) AS cd_atendente_chamado_texto
FROM sd_atividade_chamado")

5- Profit...... From there you can work however you want. 

Hope this can help someone because I had a project on hold for over a year over this... 

[TomiKasurinen]

Did you find the solution for this problem? Im facing this same problem after the database was updated, before that everything worked well.

[v-juanli-msft]

Hi @rbcbasic 

Could you show me what format of your date column from data source?

Please check if you turn on the "Auto detect column type" and set a proper locale.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Seamark]

@v-juanli-msft I have the exact same issue and have tried with and without automatically detect column settings and get the same error.

@rbcbasic Have you found a solution to this issue yet?

Kind Regards