CODEWITHSUNDEEP
google-bigquery
DWPro
DWPro

Reputation: 319

How to convert Timestamp to Date Data Type in Google Bigquery

I am trying to convert Timestamp data type columns to Date datatype using:

bq query -q --destination_table=NEW_DATE_TABLE --replace "SELECT DATE(CURR_DT) AS CURR_DT from TEST.DATE_TABLE"

The new table shows the column as STRING rather than date. Is there a way to convert timestamp to date data type.

Requested Screenshot

Upvotes: 26

Views: 99063

Answers (3)

abdelhedi hlel
abdelhedi hlel

Reputation: 3729

Try this

SELECT TIMESTAMP_SECONDS(CAST(CURR_DT AS INT64)) AS CURR_DT FROM TEST.DATE_TABLE

Upvotes: 3

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

If you use Standard SQL, you can do the following:

SELECT * REPLACE(EXTRACT(DATE FROM curr_dt)) AS curr_dt FROM test.date_table

If curr_dt is repeated field, then the solution will look the following:

SELECT * REPLACE(
  ARRAY(
    SELECT EXTRACT(DATE FROM curr_dt) FROM t.curr_dt
  ) AS curr_dt)
FROM test.date_table t

Upvotes: 25

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173171

Consider below!
Works in both Legacy and Standard SQL 

SELECT CAST(DATE(CURR_DT) AS DATE) AS CURR_DT FROM TEST.DATE_TABLE

Added to address comment

Try below - as I mentioned above - it works for both Legacy and Standard 

SELECT CAST(DATE(CURR_DT) AS DATE) AS CURR_DT
FROM (SELECT CURRENT_TIMESTAMP() AS CURR_DT)

if you are interested in making your case working with Legacy SQL - provide more details about CURR_DT field

Upvotes: 17

Related Questions