How to filter by date in Google Sheets?

Question

I have a basic table with simple data. I need to filter data by date, for example, I want to see only items with dates between 03/03/2016 and 03/04/2016.

This query looks like this:

select A, B WHERE A >= 42432 AND A <= 42463

If I change date in cells B1 and C1, numbers (42432 and 42463) are changing as well. But items are not being filtered.

So what should I do?

Answer 1

Filtering dates with the query() function requires special syntax: date 'yyyy-mm-dd'.

=query(A:B,"select A, B WHERE B >= date '2016-02-03' AND B <= date '2016-04-03'")

If you need to reference a cell with dates in them, the cells will have dates as numbers. You'll need to convert the format to yyyy-mm-dd like so:

=query(A:B,"select A, B WHERE B >= date '"&text(C1,"yyyy-mm-dd")&"' AND B <= date '"&text(D1,"yyyy-mm-dd")&"'") 

Ref docs here.