String to date time conversion Bigquery
google-bigquery
Solution
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
Problem
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