String to date time conversion Bigquery

Question

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

Answer 1

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