title: google bigquery select from a timestamp column between now and n days ago
body: <p>I have a dataset in bigquery with a TIMESTAMP column "register_date" (sample value "2017-11-19 22:45:05.000 UTC" ).</p>
  
  <p>I need to filter records based on x days or weeks before today criteria. 
  Example query 
  select all records which are 2 weeks old.</p>
  
  <p>Currently I have this query (which I feel like a kind of hack) that works and returns the correct results</p>
  
  <pre><code>SELECT * FROM `my-pj.my_dataset.sample_table`
  WHERE
   (SELECT
    CAST(DATE(register_date) AS DATE)) BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -150 DAY)
    AND CURRENT_DATE()
  LIMIT 10
  </code></pre>
  
  <p>My question is do I have to use all that CASTing stuff on a TIMESTAMP column (which seems like over complicating the otherwise simple query)?
  If I remove the CASting part, my query doesn't run and returns error.</p>
  
  <p>Here is my simplified query</p>
  
  <pre><code>SELECT
    *
  FROM
    `my-pj.my_dataset.sample_table`
  WHERE
    register_date BETWEEN DATE_ADD(CURRENT_DATE(), INTERVAL -150 DAY)
    AND CURRENT_DATE()
  LIMIT
    10
  </code></pre>
  
  <p>that results into an error</p>
  
  <pre><code>Query Failed
  Error: No matching signature for operator BETWEEN for argument types: TIMESTAMP, DATE, DATE. Supported signature: (ANY) BETWEEN (ANY) AND (ANY) at [6:17]
  </code></pre>
  
  <p>any insight is highly appreciated.</p>