Forum Discussion
Helper IDesktop error where datetime and timestamp insmatch after removing [Implementation="2.0"]
Hi #community,
I have a table in Big query which has around 130 Million rows in Big query(The table has a datetime column). so I have setup a incremental refresh to reduce the refresh time on power BI services. since september first week I have been facing the ReadChunkyRetires error in serivces so after searching on this platform I came to know about the drivers issue.
As per the suggestions given in the thread I remove the [Implementation="2.0"] from the GoogleBigQuery.Database([Implementation="2.0"]) part of the source but, when I applied the changes I got an error in the desktop saying -
google_search_console
OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000]
[Microsoft][BigQuery] (70) Invalid query: No matching signature
for operator >= for argument types: DATETIME, TIMESTAMP
Signature: T1 >= T1 Unable to find common supertype for
templated argument <T1> Input types for <T1>: {TIMESTAMP,
DATETIME} at [13:7].
Both the source data and parameters used for incremental refresh are datetime type.
This error isn't appearing when I use the [Implementation="2.0"] in the M code.
Please suggest on how to resolve this issue.
Thanks,
Regards,
Rishab Jain
Hi rishabjain237 - Convert your parameter type to match the table’s column type. If your column is DATETIME, ensure the parameter is passed as DATETIME, not TIMESTAMP.
Change your M query to explicitly cast the parameter as DATETIME
let
StartDateTime = DateTime.ToText(IncrementalStartDate, "yyyy-MM-dd HH:mm:ss"),
Source = Odbc.Query("dsn=BigQueryDSN",
"SELECT * FROM `your_project.your_dataset.your_table`
WHERE DateTimeColumn >= DATETIME('" & StartDateTime & "')")
in
SourceThis way, the IncrementalStartDate is converted to string in the correct format and passed as a DATETIME literal in the query.Once you handle the type correctly, removing [Implementation="2.0"] will work because the query no longer fails on type mismatch.
Hope this helps.
4 Replies
- rajendraongole1
Super User
Hi rishabjain237 - Convert your parameter type to match the table’s column type. If your column is DATETIME, ensure the parameter is passed as DATETIME, not TIMESTAMP.
Change your M query to explicitly cast the parameter as DATETIME
let
StartDateTime = DateTime.ToText(IncrementalStartDate, "yyyy-MM-dd HH:mm:ss"),
Source = Odbc.Query("dsn=BigQueryDSN",
"SELECT * FROM `your_project.your_dataset.your_table`
WHERE DateTimeColumn >= DATETIME('" & StartDateTime & "')")
in
SourceThis way, the IncrementalStartDate is converted to string in the correct format and passed as a DATETIME literal in the query.Once you handle the type correctly, removing [Implementation="2.0"] will work because the query no longer fails on type mismatch.
Hope this helps.
v-achippaCommunity Support
Hi rishabjain237,
Thank you for reaching out to Microsoft Fabric Community.
Thank you rajendraongole1 for the prompt response.
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
- v-achippa
Hi @rishabjain237,
We wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa
- v-achippa
Hi @rishabjain237,
As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user for the issue worked? or let us know if you need any further assistance.
Thanks and regards,
Anjan Kumar Chippa