Comparing dates in Google Sheets QUERY() formula

Question

Looking for some assistance please.

To start, here's the function that I'm having trouble with:

=IFERROR(
  QUERY(
    OrderDetails!A8:Q9,
    "SELECT SUM(J) where Q >= date '" & TEXT(D3,"yyyy-mm-dd") & "' label sum(J) ''"
  ),
  0
)

The dates in the data range (OrderDetails!A8:Q9, columns P is Date & Q is DateOnly) look like this:

data range

I added Q manually in an attempt to make the date-matching work, but P is the raw data which I would prefer to use.

Next the SUM(J) which are just order balances. If I remove the WHERE clause the query runs as expected.

D3 is the column date I want to match to, in the format: 8/13/2018, however I've formatted it on screen to be only DDD.

D3 Date

To show the actual value rather than the header in the cell, I've used label sum(J) ' '.

When running I get the message "Nothing to return".

Can anybody spot an obvious error with the code or my approach? Happy to add any further detail if needed.

have you tried datevalue(d3) where you have d3? It should be superfluous, but just in case? — Jeremy Kahan
I've just tried adding that in and I get an new error "DATEVALUE parameter '13' cannot be parsed to date/time." I assume the 13 is the day number and perhaps it's parsing in the wrong format or something. — JeffreyShran
That cell reference is incorrect, it should be D2, not D3. The new error is that it can't parse "Mon". I added the date unformatted in the style dd/mm/yyyy to a new row and removed the iferror check. and I get the same response as previously about "nothing to return". — JeffreyShran
It might be worth (without surrounding query) checking that =TEXT(whichever it is now,"yyyy-mm-dd") is what you think it is. Maybe you already did that. — Jeremy Kahan
Yes, I did check. Thanks though. :) It's returning 2018-08-13 as expected. — JeffreyShran

pnuts pnuts · Accepted Answer · 2018-08-14T14:43:51

Populate Q8 with:

=left(P8,10)*1

and copy down.

The QUERY is failing for attempting to compare a date (in D2/3) with the output of a string function (LEFT). *1 coerces the strings into dates. Left alignment was a clue that the contents were Text.

Comparing dates in Google Sheets QUERY() formula

1 Answers