Reputation: 6030
Conversion rules in Google say that TIMESTAMPS
can become DATETIME
.
The following code works to turn a TIMESTAMP
to a DATE
.
SELECT CAST( DATE(CURRENT_TIMESTAMP()) AS DATE)
We can get a DATETIME
using this in Standard SQL but we lose the time in the process:
SELECT CAST ( CAST( DATE(CURRENT_TIMESTAMP()) AS DATE) AS DATETIME )
How do we get a TIMESTAMP
to become a DATETIME
without losing the time (i.e. something like this)?
SELECT CAST( CURRENT_TIMESTAMP() AS DATETIME )
How do we do this in Legacy SQL and Standard SQL on BigQuery?
NOTE: We just discovered our example CAST works on Standard SQL (it appears our query system was using Legacy SQL).
Upvotes: 11
Views: 33500
Reputation: 173171
You can simply use CAST(... as DATETIME) as in below example
#standardSQL
WITH `project.dataset.table` AS (
SELECT CURRENT_TIMESTAMP() ts_as_timestamp
)
SELECT ts_as_timestamp, CAST(ts_as_timestamp AS DATETIME) ts_as_datetime
FROM `project.dataset.table`
with result
Row ts_as_timestamp ts_as_datetime
1 2019-06-13 19:22:42.839108 UTC 2019-06-13T19:22:42.839108
For Legacy SQL
#legacySQL
SELECT ts_as_timestamp, CAST(STRING(ts_as_timestamp) AS DATETIME) ts_as_datetime
FROM (SELECT CURRENT_TIMESTAMP() ts_as_timestamp)
with same output
Upvotes: 8