The Query function is always a topic of interest to me. I’ve written several tutorials related to this function. But still, there are some missing elements. The toDate scalar function in Google Sheets Query is one of them.

As far as I know, Query has fourteen scalar functions.

I have already covered most of them. You can check that inside my Query function guide here – Learn Query Function with Examples in Google Sheets.

Do not get confused the name toDate with TO_DATE.

Even if we pronounce them alike, both are different in use.

The latter is a native Google Sheets function to convert a provided number (date value) to a date.

E.g.: =to_date(45035)

But the toDate scalar function in Google Sheets Query is different.

Must Read: How to Utilise Google Sheets Date Functions (Complete Guide).

In this spreadsheet tutorial, let’s learn how to use the toDate scalar function in Google Sheets Query.

Let’s start with a TO_DATE and toDate comparison.

TO_DATE Vs toDate Scalar Function in Google Sheets Query

There is no scope for comparison between these two functions as both are different in use.

TO_DATE() Native Spreadsheet Function

If you have a date value, for example, 43949, in cell A1, you can convert it back to date.

=to_date(A1)

The result would be 28 April 2020.

This function is handy in some cases. Here is one example of the real-life use of the To_Date spreadsheet function in Google Sheets.

Key the below SEQUENCE formula in cell A1 (before inserting it, make the range A1:A10 blank).

=sequence(10,1,date(2020,4,1))

The result would be some date values (numbers).

With the help of TO_DATE, let’s convert these date values (numbers) into dates.

=ArrayFormula(to_date(sequence(10,1,date(2020,4,1))))

We must additionally use the ArrayFormula as above because TO_DATE is a non-array function.

We would get the dates from 01-Apr-2020 to 10-Apr-2020.

toDate() Scalar Function

As I mentioned, toDate() is a scalar function in Google Sheets Query. We can use it in the following two scenarios.

1. To match a date in the DateTime (timestamp) column.
2. To match a date in time in the millisecond (after epoch) column.

I’ll give you a few examples to explain these two toDate() functionalities below.

The Role of the toDate Scalar Function in Google Sheets Query

Here are some of the use of the toDate scalar function in Google Sheets Query.

1. Convert Timestamp to Date Using Query

To convert a timestamp to date, we usually use the INT function as detailed in my guide here – How to Extract Date From Timestamp in Google Sheets.

For example, the value in cell B2 is the DateTime 1/4/2020 10:10:10.

=to_date(int(B2))

The above TO_DATE and INT combo would return the result as 1/4/2020.

What about timestamps in an open range like B2:B?

In an open range, if any of the cells in B2:B is blank, the result would be the date 30/12/1899 against that cell.

=ArrayFormula(to_date(ArrayFormula(int(B2:B))))

The solution is an IF and ISBLANK combination with the above formula, which will skip blank cells.

=ArrayFormula(to_date(IF(isblank(B2:B),,int(B2:B))))

Using the toDate scalar function in Google Sheets Query, we can easily convert a Timestamp column to a Date column.

=query(B2:B,"Select toDate(B)")

Filter DateTime Column Using Date Criterion in Query

Let’s see how to filter all the rows in the range A1:B (please see the image above for the data range) wherever the DateTime in column B matches the date 02-Apr-2020.

Please note the criterion is 02-04-2020, which doesn’t contain time.

=query(A1:B10,"Select * where toDate(B)=date '2020-04-02'")

We can enter the date criterion in any cell, for example, in cell F1, and use the Query as below.

=query(A1:B10,"Select * where toDate(B)=date '"&TEXT(F1,"yyyy-mm-dd")&"'")

If the criterion is also DateTime, please follow this approach – How to Filter Timestamp in Query in Google Sheets.

2. Convert Date or Timestamp in Milliseconds to Date Using the toDate Function in Query

You will hopefully get some ideas about time in milliseconds after the epoch from the following two tutorials. So I am not going into that details here.

• Convert Unix Timestamp to Local DateTime and Vice Versa in Google Sheets.
• How to Convert Timestamp to Milliseconds in Google Sheets.

Here my point is you can use the toDate() scalar function in Google Sheets Query to easily convert Unix epoch timestamp in milliseconds to a proper date. Here is my example of the same.

=query(A1:B,"Select A,toDate(B)")

Please note that we can also use the native Google Sheets function named EPOCHTODATE for that.

Filter a Millisecond Column Using Date Criterion in Query in Google Sheets

Assume I want to filter the above data in A1:B using a date criterion in Query.

I’ll use the toDate scalar function in Google Sheets Query as below to filter the data wherever the DateTime in milliseconds is equal to 01-Apr-2020.

=query(A1:B,"Select * where toDate(B)=date '2020-04-01'")

Alternatively, insert the criterion 01-Apr-2020 in cell D1 and use the below Query.

=query(A1:B,"Select * where toDate(B)=date '"&TEXT(D1,"yyyy-mm-dd")&"'")

Must Read: How to Use Date Criteria in Query Function in Google Sheets.

That’s all. Enjoy!