CODEWITHSUNDEEP
sqlgoogle-bigquery
Simon Breton
Simon Breton

Reputation: 2876

Invalid date error when modifying Big Query Table schema from string (YYYY-MM-DD HH:MM:SS TIMEZONE) to date

I have a table with a string format column where value are like this :

YYYY-MM-DD HH:MM:SS TIMEZONE so for example 2015-08-27 19:42:53 UTC

UTC is the only timezone. I want to rewrite this string column as a date column. I have an Invalid date error when I'm trying to run the following query to export the result in a new table 

SELECT
  CAST(my_date AS DATE),stuff_here,stuff_here
FROM
  `table`

What should I do in order to properly change the type of this column from string to date ?

Upvotes: 0

Views: 2705

Answers (2)

FDavidov
FDavidov

Reputation: 3675

Try:

SELECT CAST(SUBSTR(my_date,1,19) AS DATE,stuff_here,stuff_here
  FROM `table`

This assumes that you are not interested in the timezone part.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You appear to be trying to write this logic:

select CAST(substr('2015-08-27 19:42:53 UTC', 1, 10) AS DATE)

Because I am in New York, I would instead write:

select date(timestamp('2015-08-27 19:42:53 UTC'), 'America/New_York')

This distinction has been very important in our using data -- the difference between days at the Greenwich Meridian versus in our local area.

Upvotes: 1

Related Questions