Google Query date specifications

(This is purely a rewriting of Ben Collins' excellent article so that I can grasp it easier)

• Google Query has a keyword that introduces a date literal, date 'yyyy-mm-dd', and like other parts of GQ language it's limited and inflexible. You have to give it the format shown.
• If your date criterion is a hard-coded date, you can just put that date in the string, eg Col2 >= date '1997-05-01'
• But if as is more likely, your date comes from a spreadsheet cell, you have to manipulate it into yyyy-mm-dd format.
  • Spreadsheet function DATEVALUE(s) interprets s as a date in the same way that Google Sheets would if you literally typed s into a cell, ie using the locale settings you've got set for GS, and it returns a GS date number
  • Spreadsheet function TEXT(n, f) applies a format f to a number n to give a formatted string. Eg TEXT(0, "YYYY-MM-DD") = "1899-12-30"
• So you simply apply DATEVALUE to your spreadsheet date, and TEXT( , "yyyy-mm-dd") to the result, and concatenate that into your query: "Col2 >= date '" & TEXT(DATEVALUE(thedate), "yyyy-mm-dd") & "'"

Thanks again, Ben.