title: SQL convert yyyymmdd to timestamp in BigQuery?
body: <p>I'm attempting to convert a string to a timestamp within SQL. The question is really quite simple, how can I convert this string into a timestamp that starts at midnight on that day?</p>
  
  <p>Within my database I also have a field stored in <code>timestamp_micros</code> either one of these could work and I think converting the micros to a timestamp would be easier than the string. </p>
  
  <p>For example
  <code>20170118 =&gt; timestamp</code></p>
  
  <p>Query:</p>
  
  <pre><code>WITH allTables as (
    SELECT
        event.date as date,
        count(*) as totalSessions,
        count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
    FROM `namehiddenonlyhere.*`
    CROSS JOIN
        UNNEST(event_dim) AS event
    WHERE
        event.name = 'session_start'
    AND 
        event.date &lt;= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
    AND
        event.date &gt;= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
    GROUP BY event.date
  
    UNION ALL 
    SELECT
        event.date as date,
        count(*) as totalSessions,
        count(DISTINCT user_dim.app_info.app_instance_id) as uniqueUsers
    FROM `namehiddenonlyhere.*`
    CROSS JOIN
        UNNEST(event_dim) AS event
    WHERE
        event.name = 'session_start'
    AND 
        event.date &lt;= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY))
    AND
        event.date &gt;= FORMAT_DATE('%Y%m%d', DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY))
    GROUP BY event.date
  ) SELECT 'all apps' as target, date as datapoint_time, totalSessions datapoint_value FROM allTables;
  </code></pre>