CODEWITHSUNDEEP
google-bigquery
Maulik Dholaria
Maulik Dholaria

Reputation: 507

String to date time conversion Bigquery

I have a source table which has dates stored in string format as below:

1984-10-28 00:00:00
1988-11-22 00:00:00 
1990-01-08 00:00:00
1983-06-22 00:00:00

Then created a table test1 as below:

mk -t xyz.test birthdate:timestamp

When I write a query to convert all the dates from source table to timestamp and save it to test1 it gives an error:

SELECT FORMAT_UTC_USEC(birthdate) AS birthdate FROM [sourcetable] LIMIT 10

Error: Invalid schema update. Field birthdate has changed type
Job ID: [Project]:job_5P4gFVl4wiYuyXL0vY5VZNtCZOU

Please help if I am doing anything wrong

Upvotes: 8

Views: 22719

Answers (2)

Maulik Dholaria
Maulik Dholaria

Reputation: 507

Never mind. I found it. I was overlooking a big part of bigquery's query reference document.

TIMESTAMP()
Convert a date string to a TIMESTAMP data type.

SELECT TIMESTAMP("2012-10-01 01:02:03");

Returns: 2012-10-01 01:02:03 UTC

So the correct query as below: SELECT TIMESTAMP(birthdate) AS birthdate FROM [sourcetable] LIMIT 10

Upvotes: 12

Jordan Tigani
Jordan Tigani

Reputation: 26617

You're running a query that updates a table in place? Are you using WRITE_TRUNCATE? If not, you'll just end up appending to the table with a different schema.

Upvotes: 0

Related Questions