1

Hey all,

I’ve got a lot of queries and most of them I have set to refresh on a schedule and none of them have any problems. But recently we changed a query to accept range of dates in the parameters in order to filter the data. Yesterday I did a test refresh, got the job ID, checked the job, and it returned with a successful “status: 3” and newly generated query_result_id. I tried a couple more date ranges for testing and everything looked good.

Then today I ran another test refresh. Checked the job and I had a “status: 4” with this response:

{
"job": {
    "status": 4,
    "error": "invalid input syntax for type date: \"\"\nLINE 5: ... cast(_sdc_source_key_date_start AS DATE) >= cast('' AS DATE...\n                                                             ^\n",
    "id": "69b1cc7-069b-4d27-9e0a-3b1a069b1cc7",
    "query_result_id": null,
    "updated_at": 0
}
}

I checked the dates in the params, made sure they weren’t malformed - they weren’t, in fact they were dates I had successfully tested yesterday. Started trying different date ranges - all resulted in the same error. This is the only query (so far) that this has occurred with and I’m kind of baffled because no changes were made to the query and no changes were made in the API call, so I’m having trouble understanding the catalyst for this error.

Example API endpoint:
http://{host}/api/queries/1/refresh?api_key={api_key}&p_time_period.start=2020-03-12&p_time_period.end=2020-03-17

Any help would be much appreciated!

2

Can you share an example of the SQL in question? There’s not enough detail here for us to help.