Generate a column of random dates in BigQuery

Question

How would I go about creating a new column of randomly selected dates with bigquery? This is closely related to this question, but the dates should be random and not joined by anything. Ideally, it would just be a column of IDs and a column of random dates in a given range:

ID    Date
1     2010-01-01
2     2012-03-31
3     2011-07-11
4     2015-06-09

This is easier on regular sql, but I cannot get this approach to work with the bigquery sql syntax.

Answer 1

Below is for BigQuery Standard SQL

#standardSQL
WITH parameters AS (
  SELECT 100 ids_count, DATE '2010-01-01' start_date, DATE '2020-12-31' finish_date
)
SELECT id, DATE_FROM_UNIX_DATE(CAST(start + (finish - start) * RAND() AS INT64)) random_date
FROM parameters, 
UNNEST(GENERATE_ARRAY(1, ids_count)) id,
UNNEST([STRUCT(UNIX_DATE(start_date) AS start, UNIX_DATE(finish_date) AS finish)])
-- ORDER BY id