google-sheetsgoogle-sheets-formulagoogle-formsgoogle-query-language

How to use date with LIKE inside query?


I've got a google form(which feeds to Google sheets) for users to enter information. One of the fields is a datepicker which has this format mm/dd/yyyy.

I have a workbook with several sheets. One sheet (tab) captures the user's input from the google docs form. It's called Form Responses 1. (for example)

On another tab, I'm trying to extract data based on month that comes from column G.

For example, in the first cell, I've tried formulas like this (trying to select all December answer from 12/1/2025 to 12/31/2025):

=query('Form Responses 1' !A2:N, "Select * Where G like '12')

=query('Form Responses 1' !A2:N, "Select * Where G >= '12/1/2025' and <= '12/31/2025')

Anyone know how to get either a LIKE '12%' to work on the datepicker field answer (I know it's not a string) or some other way not using a LIKE that will return every answer with 12 in the month, or mm position?


Solution

  • It's important to note that query uses yyyy-MM-dd format for date internally, regardless of what format you use on the outside(as long as it is recognized as a date format by Google sheets). So, like would work like this:

    Any year's December:
    "Select * Where G like '%-12-%'
    
    2024th December:
    "Select * Where G like '2024-12-%'
    
    2024th December 10th-19th day:
    "Select * Where G like '2024-12-1_'
    

    if G is datetime(not just date), we need an extra wildcard at the end

    "Select * Where G like '2024-12-1_%'
    
    2020-2029, Any month, 20th-29th:
    "Select * Where G like '202_-%-2_'
    

    if G is datetime(not just date), we need an extra wildcard at the end

    "Select * Where G like '202_-%-2_%'
    
    Any date with number 12 anywhere(year 2012 or 12th of 2025 or December):
    "Select * Where G like '%12%'
    

    Do note that the scalar functions inside query like month offer different control.

    Any year's December(11th month as months start from 0(not 1)):
    "Select * Where month(G)=11