How to use date with LIKE inside query?

Question

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?

LIKE used for partially matching or wildcard matching. In case of date you need similar BETWEEN in google-sheet. So use > and < greater than and less than operator. — Harun24hr
– Harun24hr, Commented Nov 16, 2024 at 4:43
It's impossible to have a "google docs excel form". Google Docs is owned by Google, and Excel is owned by Microsoft. You either have a Google Sheets form (which means Excel isn't involved), or you have an Excel form (in which case Google Sheets is not involved). Please edit either your question title and/or the tags to reflect what you're actually using and asking about. — Ken White
– Ken White, Commented Nov 16, 2024 at 5:13

Harun24hr · Accepted Answer · 2024-11-16 04:19:38Z

3

Let assume your date picker cell is B2 then use following QUERY() formula-

=QUERY('Form Responses 1' !A2:N,"select * where G>= date '" &TEXT(B2,"yyyy-mm-dd")& "' and G<= date '" & TEXT(EOMONTH(B2,0),"yyyy-mm-dd")& "'")

If you want to put dates manually then try-

=QUERY('Form Responses 1' !A2:N,"select * where G>= date '" &TEXT(DATE(2025,12,1),"yyyy-mm-dd")& "' and G<= date '" & TEXT(DATE(2025,12,31),"yyyy-mm-dd")& "'")

answered Nov 16, 2024 at 4:19

Harun24hr

38.1k6 gold badges24 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

doubleunary · Accepted Answer · 2024-11-30 09:24:51Z

2

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:

"where G like '%-12-%' "

2024th December:

"where G like '2024-12-%' "

2024th December 10th-19th day:

"where G like '2024-12-1_' "

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

"where G like '2024-12-1_%' "

2020-2029, Any month, 20th-29th:

"where G like '202_-%-2_' "

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

"where G like '202_-%-2_%' "

Any date with number 12 anywhere(year 2012 or 12th of 2025 or December):

"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)):

"where month(G) = 11"

...or:

"where month(G)+1 = 12"

edited Nov 30, 2024 at 9:24

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

answered Nov 16, 2024 at 17:05

TheMaster

51.6k7 gold badges76 silver badges102 bronze badges

3 Comments

doubleunary Over a year ago

Looks good, but Select * is superfluous. In query language, all clauses are optional, including select.

We5inelgr Over a year ago

Thank you all for the replies, I appreciate it! I wound up using ""Select * Where G like '2024-12-%'" so as to not pick up 3rd day and or 3rd month, if searching for '3' or March for example. Learning that query uses yyyy-MM-dd as the format for date was they key to understanding this for me. Thanks!

TheMaster Over a year ago

@doubleunary I'm aware, but forgot that this moment... guess I needed coffee...

doubleunary · Accepted Answer · 2024-11-16 10:20:33Z

1

(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')

To do that with hard-coded parameters, use year() and month(), like this:

=query('Form Responses 1' !A2:N, "where year(G) = 2025 and month(G)+1 = 12", 0)

To get rows that are in the same month as the date in cell D1, use this:

=query( 
  'Form Responses 1' !A2:N, 
  "where year(G) = " & year(D1) & 
  "and month(G)+1 = " & month(D1), 
  0 
)

See query(), year() and month().

answered Nov 16, 2024 at 10:20

doubleunary

21.9k13 gold badges38 silver badges85 bronze badges

Comments

Ganesh Chandak · Accepted Answer · 2024-11-30 08:25:37Z

0

The easiest way is to use Filter, month, and year:

=filter('Form Responses 1' !A2:N,month('Form Responses 1' !G2:G)=12,year('Form Responses 1' !G2:G)=2025)

answered Nov 30, 2024 at 8:25

Ganesh Chandak

612 bronze badges

Collectives™ on Stack Overflow

How to use date with LIKE inside query?

4 Answers 4

Comments

Any year's December:

2024th December:

2024th December 10th-19th day:

2020-2029, Any month, 20th-29th:

Any date with number 12 anywhere(year 2012 or 12th of 2025 or December):

Any year's December(11th month as months start from 0(not 1)):

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Any year's December:

2024th December:

2024th December 10th-19th day:

2020-2029, Any month, 20th-29th:

Any date with number 12 anywhere(year 2012 or 12th of 2025 or December):

Any year's December(11th month as months start from 0(not 1)):

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related