title: Date and time functions in BigQuery's Standard SQL
body: <p>I was trying to use one of my MSSQL queries on BigQuery's new standard SQL feature, as it requires special JOIN conditions. However, the reference doesn't contain any explanations of the date time functions in BigQuery's standard SQL yet.</p>
  
  <p>So far I managed to convert my DATE_ADD statements
  from: <em>DATE_ADD('2015-01-01', -13, 'MONTH')</em>
  to: <em>DATE_ADD('2015-01-01', INTERVAL -13 MONTH)</em></p>
  
  <p>I also converted YEAR(x) to EXTRACT(YEAR FROM x). Both seem to be MySQL-like statements.</p>
  
  <p>Lastly I noticed the output of most date functions must be converted using TIMESTAMP before you can compare them.</p>
  
  <p>But now that my query finally wants to run I get an <em>"Internal error"</em>. Anyone know what could be wrong? This is the query:</p>
  
  <pre><code>SELECT
    M,
    Bought12MBeforeM,
    Bought12MBeforeAndInM,
    (Bought12MBeforeAndInM * 1.0) / (Bought12MBeforeAndInM + Bought12MBeforeM) AS RepurchaseRate
  FROM (
    SELECT
      M,
      COUNT(DISTINCT CASE WHEN MaxM &lt; TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeM,
      COUNT(DISTINCT CASE WHEN MaxM &gt;= TIMESTAMP(M)
        AND MinM &lt; TIMESTAMP(M) THEN user_id ELSE NULL END) AS Bought12MBeforeAndInM
    FROM (
      SELECT
        M,
        user_id,
        MAX(cart_creation_date) AS MaxM,
        MIN(cart_creation_date) AS MinM
      FROM (
        SELECT
          user_id,
          cart_creation_date
        FROM
          `vex-eu-cloud-sql-001.work_user.flat_orders`
        WHERE
          cart_creation_date &gt;= TIMESTAMP(DATE_ADD('2015-01-01', INTERVAL -13 MONTH))
        GROUP BY
          user_id,
          cart_creation_date ) UniqueCarts
      INNER JOIN (
        SELECT
          DATE(Day) AS M
        FROM
          `vex-custom.wouter.days_2007_2020`
        WHERE
          EXTRACT(YEAR
          FROM
            Day) &gt;= 2015
          AND EXTRACT(DAY
          FROM
            Day) = 1
          AND Day &lt; TIMESTAMP(CURRENT_DATE()) ) Months
      ON
        cart_creation_date &gt; TIMESTAMP(DATE_ADD(M, INTERVAL -13 MONTH))
        AND cart_creation_date &lt; TIMESTAMP(DATE_ADD(M, INTERVAL 1 MONTH))
      GROUP BY
        M,
        user_id ) BoughtInM
    GROUP BY
      M ) Results
  </code></pre>