Syntax error for Where clause in Bigquery-CodePudding

I am getting an error when I try to use where clause in the following query :

SELECT  
  creation_date
FROM 
  `bigquery-public-data.stackoverflow.stackoverflow_posts`
WHERE
  creation_date BETWEEN 2021-08-01 AND 2021-08-31;

Syntax error : No matching signature for operator BETWEEN for argument types: TIMESTAMP, INT64, INT64.  Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:17]

What is the correct way to do it? I am trying to create daily aggregate table for month of August combing data from 2 tables.

CodePudding user response：

Just look at the actual data types you are dealing with: following will work: creation_date is timestamp data type so cast to DATE and use quotes for static dates.

SELECT  
  creation_date
FROM 
  `bigquery-public-data.stackoverflow.stackoverflow_posts`
WHERE
  DATE(creation_date) BETWEEN '2013-01-02' AND '2021-08-31';

CodePudding user response：

I would try either this

SELECT  
  creation_date
FROM 
  `bigquery-public-data.stackoverflow.stackoverflow_posts`
WHERE
  creation_date BETWEEN '2021-08-01' AND '2021-08-31';

or this

SELECT  
  creation_date
FROM 
  `bigquery-public-data.stackoverflow.stackoverflow_posts`
WHERE
  creation_date BETWEEN #2021-08-01# AND #2021-08-31#;