Issue regarding datetime filter while copying data from Shopify to Synapse Analytics

Question

Hi,

I've tried copying data from Shopify to the Dedicated SQL Pool.

It works when I copy the whole table from Shopify, but filtering with date column doesn't work.

I tried with various datetime format as a parameter, but I kept receiving error messages like the one below.

2023-10-12T19:09:30

2023-10-11T00:00

2023-10-11 00:00:00

2023-10-11 00:00

2023-10-11T00:00:00Z

2023-10-11T00:00:00-04:00

{
    "errorCode": "2200",
    "message": "Failure happened on 'Source' side. ErrorCode=UserErrorUnclassifiedError,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=Odbc Operation Failed.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=System.Data.Odbc.OdbcException,Message=ERROR [22018] [Microsoft][Support] (40550) Invalid character value for cast specification.,Source=ShopifyODBC_sb64.dll,'",
    "failureType": "UserError",
    "target": "Copy_ruj",
    "details": []
}

Any help would be appreciated. Thanks.

Answer 1

@Daniel Kwon Welcome to Microsoft Q&A platform and thank you for reaching out.
As I understand you're trying to various datetime formats as a parameter, but they keep receiving error messages. Please let me know if that is not the case.
As per the error message you are receiving indicates that the Shopify ODBC driver is unable to convert the date value that you are passing to it. To troubleshoot this issue, you can check the ODBC driver version and ensure that it is compatible with the version of Shopify you are using. You can also verify that the connection string is correct and that the credentials are valid.
In general, the datetime format should follow the ISO 8601 standard, which is YYYY-MM-DDTHH:MM:SSZ.
To filter data with a date column while copying data from Shopify, you can use the SQL query language. You can specify the date range in the WHERE clause of the SQL query as below.
SELECT * FROM "Shopify"."Orders" WHERE "Created_at" >= '2023-10-11T00:00:00Z'

I hope this helps you resolve your issue. Let me know if you have any further questions or concerns.