How to compare a DATE with a TEXT string in a QUERY function in Google Sheets?

Question

Goal: Return a specific value from a table of data based on a query (kinda like if VLOOKUP provided the option for multiple criteria).

Problem: The data in the source table is a value and I can't change the data source's format. When I run my QUERY function I get #N/A. I know it's due to the data type of the source table data because when I update the format to "plain text" the value works.

Here is my Query:

=QUERY(SessionsData,"select D where B='"&TEXT(Date(YEAR(TODAY()),4,$A143),"yyyy-MM-dd")&"' limit 1",0)

I know the logic works, watch this video for a brief demo.

How can I get this comparison to return results?

Answer 1

Adapted from this answer:

The Query language has two functions to help with date comparisons.

• The todate() scalar function will convert spreadsheet dates (like your column B) to query date values. If the value started as a datetime, it returns just the date portion.
• The date modifier treats specifically formatted strings as query date values.

Use them like so:

=QUERY(SessionsData,"select D where todate(B)=date '"&TEXT(Date(YEAR(TODAY()),4,$A143),"yyyy-MM-dd")&"' limit 1",0)
                                    ^^^^^^^^^ ^^^^