Convert DATE to TIMESTAMP on BigQuery/StandardSQL

Question

I am querying a table on BigQuery that has a field in the 'DATE' format. I want to read this in the 'TIMESTAMP' format. I tried converting the DATE to an integer and then converting into a TIMESTAMP but doesn't seem to work.

Answer 1

#standardSQL
WITH `project.dataset.table` AS (
  SELECT CURRENT_DATE() AS dt
)
SELECT dt, CAST(dt AS TIMESTAMP) AS ts,
  TIMESTAMP(dt) AS ts2
FROM `project.dataset.table`   

with result as

Row dt          ts                           ts2
1   2018-06-13  2018-06-13 00:00:00.000 UTC  2018-06-13 00:00:00.000 UTC