I am creating tables in Big Query for the Google Data Analytics Project. I am using auto detect schema. I have 12 google sheets representing 12 months of data. The format of the 12 sheets is identical.

One column I each sheet shows elapsed time between two time stamps. The first table I created pulled in the data from that column as a string. The second table pulled the data in that column as a TIME format – this is what I want. I tried two more – same result – I resulted in a string format, and the other as a TIME format.

I can’t find anything that would indicate why auto detect is interpreting the column two different ways. I keep trying to select the entire column and make sure the format is either DURATION or a custom time format in Google Sheets and recreate the table – it doesn’t change the outcome – it still comes out as a string.

I did try to run a query to change the data type for the column using the following:

ALTER TABLE ttoth-new-project-7-20-2022.TTOTH_EOC_Project.202306_tripdata_added_cols_June_1to15

ALTER COLUMN ride_length
SET DATA TYPE TIME;

I get the following error message:
ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRING) is assignable to the new type (TIME) at [3:1]

Can someone guide me on the best way to resolve this?
THank you.

I did try to run a query to change the data type for the column using the following:

ALTER TABLE ttoth-new-project-7-20-2022.TTOTH_EOC_Project.202306_tripdata_added_cols_June_1to15

ALTER COLUMN ride_length
SET DATA TYPE TIME;

I get the following error message:
ALTER TABLE ALTER COLUMN SET DATA TYPE requires that the existing column type (STRING) is assignable to the new type (TIME) at [3:1]