Why my Google Sheet QUERY getting error when selecting date?

1
votes

I want to query data between two dates. Here is my query:

=query(Profile;"select * where B >= date '"&TEXT(DATEVALUE("27/02/2018"),"yyyy-mm-dd")&"' and B <= date '"&TEXT(DATEVALUE("01/03/2018"),"yyyy-mm-dd")&"";1)

enter image description here

and here is the spreadsheet https://docs.google.com/spreadsheets/d/1AvyMhjPMDeX6lszrrF4dqXssZSzYPuZdcbsQ66jzQOg/edit?usp=sharing

3
google-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language
What does it have anything to do with sql server? – Tanveer Badar
It has nothing to do, my mistake – Juli Andika

3 Answers

1
votes

this is the correct syntax:

=QUERY(Profile; 
 "where B >= date '"&TEXT(C3; "yyyy-mm-dd")&"' 
    and B <= date '"&TEXT(C4; "yyyy-mm-dd")&"'"; 1)

0

1
votes

It looks that a ' is missing on &"". It should be &"'"

The final formula is

=query(Profile;"select * where B >= date '"&TEXT(DATEVALUE("27/02/2018"),"yyyy-mm-dd")&"' and B <= date '"&TEXT(DATEVALUE("01/03/2018"),"yyyy-mm-dd")&"'";1)

0
votes

Change the format of the date string you are passing to mm/dd/yyyy instead of the current dd/mm/yyyy and it should work.

e.g. from @Ruben example above:

=query(Profile;"select * where B >= date '"&TEXT(DATEVALUE("02/27/2018"),"yyyy-mm-dd")&"' and B <= date '"&TEXT(DATEVALUE("03/01/2018"),"yyyy-mm-dd")&"'";1)

It seems like this is a backwards step from the formula you asked about yesterday, which deals with date formatting itself rather than manually having to pass a date as a string: