ROLLING date range in a Google Ads API Query
let startDate = '2020-01-01'; let endDate = new Date().toISOString().slice(0, 10).toString(); // "WHERE segments.date BETWEEN '2020-01-01' AND 'endDate' " Returns this error:
Exception: Call to GoogleAdsService.Search failed: Condition 'segments.date BETWEEN '2020-01-01' and 'endDate'' is invalid: BETWEEN operator must have exactly two values that are both numbers or both date strings in 'YYYY-MM-DD' format.
How can I set endDate to YESTERDAY?
Google Ads Query Language accepts dates both with and without quotes. If you use the ISO-like date format with hyphens, quotes are mandatory, though.
Assuming that this is JavaScript, you can just do
"WHERE segments.date BETWEEN '2020-01-01' AND '" + endDate + "'"Or using template literals (available from ES6 on, note the enclosing backticks):
`WHERE segments.date BETWEEN '2020-01-01' AND '${endDate}'`Getting this error Exception: Call to GoogleAdsService.Search failed: Error in query: unexpected input -. Works fine when I set it in as YYYY-MM-DD, but can't figure out why ISO format doesn't work as I need it changing every day.
Can you print the actual query before sending it, just to double-check that the required quotes are actually there?
Here it is: "WHERE segments.date BETWEEN '2020-01-01' AND " + endDate + " AND metrics.clicks > 0 " +
You're missing the single quotes from my first example. Also, not sure what the extra plus is doing at the end.
Got it, I see what the problem is now. Thank you so much, works now. The plus at the end is for the ORDER clause.
Try using: WHERE segments.date BETWEEN '2020-01-01' AND endDate
instead of WHERE segments.date BETWEEN '2020-01-01' AND 'endDate'
on preview returns: Exception: Call to GoogleAdsService.Search failed: Error in WHERE clause: invalid value endDate.
Can you try replacing endDate with YESTERDAY? @viovco
same error Exception: Call to GoogleAdsService.Search failed: Error in WHERE clause: invalid value YESTERDAY.