Problem Statement

I am trying to run a QUERY on a large data set, but cannot get my QUERY to return any data. It keeps giving me this error: "Query completed with an empty output". The crux of the issue is the format the dates are in: "2023-02-18T14:02:58.760Z". I need to be able to convert these date strings into dates the QUERY can understand without creating extra columns in my Sheets.

My approach so far and example

I began by trying to use DATEVALUE(LEFT(... to convert the dates, and have made some progress.

I have recreated a simpler example of what I'm trying to do here.

On the left is a mock-up table of data with dates in column A in the format I have to work with (for example: "2023-02-18T14:02:58.760Z"). I want to be able to filter out dates that are not relevant.

Reading this article I tried creating a virtual array to convert the string dates into dates that the QUERY can understand. I think I'm on the right track as I can make the dates show up correctly in my sheet with the following formula:

=ARRAYFORMULA(QUERY({DATEVALUE(LEFT(A2:A12,10)),A2:C12}, "SELECT Col1",0))

However, when I add the WHERE clause, no results are returned and there are clearly dates that should come back. Here's the formula with the WHERE clause:

=ARRAYFORMULA(QUERY({DATEVALUE(LEFT(A2:A12,10)),A2:C12}, "SELECT Col1 WHERE Col1 >= date '2023-02-01'",0))

Question

How can I filter out rows with dates that don't match (I actually need to have them between two dates - a start and end date)? I don't care if I use the technique above that I started pursuing. If there's a better way than the DATEVALUE(LEFT(... approach, that's fine.

One note, in my real example I am using IMPORTRANGE from another sheet and I can't edit that sheet, so I can't add columns to it, and I also don't want to add columns to the sheet I do have edit access to, because there will be many tables and I'd need a date column for each. This is why I'm trying to create virtual columns inside my QUERY.

EDIT:

Both saddles and rockinfreakshow provided answers that were useful and either could have been applied to my data as solutions to my problem.

However, ultimately I went with a similar approach to saddles because for my particular set of data and the date format (2023-02-18T14:02:58.760Z) as he pointed out, the dates can be compared as text strings without converting them to date objects. This made me realize a virtual column is not even needed in my case and there's no need for DATEVALUE(LEFT(...

Here's a modified approach based on saddles answer using a text comparison without the virtual column:

=QUERY(A2:C12, "SELECT Col1 WHERE * >= '"&TEXT("2023-02-01", "yyyy-mm-dd")&"'",0)

This works since I am not concerned with the date format returned and this gets you output like the following (with the correct rows filtered out):

If you do need to have date objects in a virtual column, or need your returned date column modified for a specific format, then try either of the solutions mentioned above.

ALTERNATIVE SOLUTION

Another approach that you may try is this:

=ARRAYFORMULA(QUERY({DATEVALUE(LEFT(A2:A12,10)),A2:C12}, "SELECT Col1 WHERE Col1 is not Null and Col2 >= '"&TEXT("2023-02-01", "yyyy-mm-dd")&"'",0))

OUTPUT

EXPLANATION

Since you're already getting the dates in your formula, I simply removed the first empty space with Col1 is not Null and used the second column as the parameter for the date you wanted because I noticed that the output of the table looks like this:

REFERENCE

• Filtering With Dates In The QUERY Function