This post includes a few Query formulas to help you understand how to use different literals in Query in Google Sheets.

The purpose is for comparison and assignments. I found the date literals are complex in use.

In Query, you can use data types like String, Number, Date, DateTime, Time of day, and Boolean TRUE or FALSE.

In such data types, for performing comparisons (<, >, etc.) or assignments (=), we can use values called literals.

With the help of the below formulas, I hope you will be able to learn how to use different literals in Query in Google Sheets.

Literals in Google Sheets Query (Formula Examples)

Please note that I have already posted several Query formula examples on this blog.

So you may already know the use of literals in Google Sheets even though I didn’t name ‘literals’ specifically.

I am writing this post solely because it will help you understand all the literals used in Google Sheets Query.

It will also help you learn the proper use of criteria in Query.

Hence you can use this post as a reference guide to clear your criteria usage doubts that occur in the future.

1. String Literals in Sheets Query

To use each literal, you must know the keywords. Let me start with strings.

String literals are case-sensitive. Further, you must include string literals within single or double quotes.

Formula Examples of the Use of String Literals within Single Quotes:

=query(A1:D,"Select * where C='Dubai'",1)

In this formula, ‘Dubai’ is an example of how to use a string literal in Google Sheets Query.

Suppose the string Dubai is in cell F1. Then the string literals in the Query formula would be as follows.

=query(A1:D,"Select * where C='"&F1&"'",1)

Formula Examples of the Use of String Literals within Double Quotes:

Use this approach to escape single quotes (apostrophes) within the string in the Query criterion.

=query(A1:D,"Select * where D=""Nature's Wisdom""",1)

Here the string literal is ""Nature's Wisdom"".

I’m sure you are keen to know how this string to use in Query as a cell reference. See the following formula!

=query(A1:D,"Select * where D="""&F1&"""",1)

2. Number Literals in Sheets Query

Since the above sample data doesn’t contain a numeric field, I am using number literals in the Query limit clause.

=query(A1:D,"Select * limit 5",1)

In the above code, 5 is an example of number literals. Here is another example formula.

Assume we have a two-column dataset in range A1:B.

If column B contains numbers, you can use the number literals as below in the Query Where clause.

=query(A1:B,"Select * where B=4",1)

The below Query formula contains a number literal as a cell reference.

=query(A1:B,"Select * where B="&F1&"",1)

Note:- We must specify the number/numeric literals in decimal notation, such as 5, 4.5, 3.0, -5.25, etc.

3. Different Date, DateTime, and Timeofday Literals in Query

The Date, DateTime, and Timeofday literals in Query are also simple.

But the single/double quotes and date formatting make it tougher to remember. So please pay attention to this literals use.

Date Literals

The date must be used in YYYY-MM-DD format as below (not in the data but in the formula).

=Query(A1:B,"Select * where B <= date '1999-12-31'",1)

I’ve used the keyword datethat followed by a string literal in the format yyyy-mm-dd.

If the date criterion is in cell F1, you can refer to that in Query as below.

=Query(A1:B,"Select * where B <= date '"&TEXT(F1,"yyyy-mm-dd")&"'",1)

Related Reading: How to Use Date Criteria in Query Function in Google Sheets.

DateTime (Timestamp) Literals

First, go thru’ the following Query formula example.

=query(A1:B,"Select * where B>= timestamp '2019-5-9 10:00:00'",1)

In this, timestamp '2019-5-9 10:00:00' is the said DateTime literal.

Instead of the “timestamp” keyword, you can use “datetime” also, like datetime '2019-5-9 10:00:00'.

Use the keyword datetimeor timestamp followed by a string literal in the format yyyy-MM-dd HH:mm:ss[.sss]. The last part within the square bracket is optional.

Query formula when the above datetime is in cell F1.

=query(A1:B,"Select * where B>= datetime '"&TEXT(F1,"yyyy-mm-dd HH:mm:ss")&"'",1)

Related Reading: How to Use DateTime in Query in Google Sheets.

Timeofday Literals in Query

In the above screenshot, if column B contains time only, not timestamp, the Query formula would be like this.

=query(A1:B,"Select * where B>= timeofday '10:00:00'",1)

Here timeofday is the keyword. The time is specified as a string literal in the format HH:mm:ss[.SSS].

Formula, when the time (criterion) is in cell F1.

=query(A1:B,"Select * where B>= timeofday '"&TEXT(F1,"HH:mm:ss")&"'",1)

4. The Use of Boolean Literals TRUE or FALSE in Query

As you may know, the Boolean literals are either TRUE or FALSE.

Note:- You can use the Boolean literals similar to number literals in Query.

Let me show you how to use the Boolean literals in Query.

=query(A1:B,"Select * where B=TRUE",1)

In this Query, you can see the use of the Boolean literals TRUE. Here is the equivalent when cell F1 contains TRUE.

=query(A1:B,"Select * where B="&F1&"",1)

I hope you find the above formula examples related to literals in Query quite useful. Enjoy!