DateTime convert error between spreadsheet and BigQuery

I got datetime convert error when I tried to select datetime column of spreadsheet.

BigQuery(BQ) is very useful for data analysis or processing.

It is good at handling huge data. It returns summary result in short time.

And we can use spreadsheet as data source of BigQuery.

But you may get such kind of message when you try to select spreadsheet data.

Error while reading table: test.datetime_sample, error message: 
Could not convert value to datetime. Row 1; Col 0.

It seems like datetime error.

But how can we solve it ?

So today I introduce about "DateTime convert error between spreadsheet and BigQuery".

Author

Mid-carieer engineer (AI, system). Good at Python and SQL.

Data

First, we have to prepare data.

Today we use spreadsheet.

step
1Prepare spreadsheet data

In order to make new spreadsheet, you should click "New" on Google Drive.

Google Drive "New"

Then you can see file options.

So click "Google (Spread)Sheet".

GoogleDrive Spreadsheet

After that input data like below.

Spreadsheet

In this case, we need datetime value. So input like this.

step
2Create table

Next create table.

In order to create table, click three dot mark … and open on right side of the dataset. Then you can see dataset space to he right.

And you see the link "CREATE TABLE". Click it.

SQL workspace CREATE TABLE

So detail window comes from right, and input details.

Settings are below.

Then table that its data source is spreadsheet is created.

But is does not have its own data.

It is just a reference table like VIEW.

So every time you select table, spreadsheet will be read.

SELECT

So try to select table that you created.

SELECT * FROM test.datetime_sample

Result is below.

It shows error and error message is written like this.

Error while reading table: test.datetime_sample, error message: 
Could not convert value to datetime. Row 1; Col 0.

It says that there is a datetime conversion error at column 0 row 1.

But on spreadsheet, it shows calendar.

So it is obviously date value.

datetime on spreadsheet

How can we modify it ?

Change datetime to string

BigQuery did not accept datetime value on spreadsheet.

Then we can try with string(text) value.

In order to change datetime value to string, we can add ' on its head like Excel.

Change datetime to string

Then try to select it.

Ooooops! It's error!

Error message is below.

Error while reading table: theta-bliss-258006.test.datetime_sample, 
error message: Could not convert value to datetime. 
Error: Invalid datetime string "2021/09/08 20:00:00". Row 1; Col 0.

It says "datetime convert error" too.

But this time it recognized input value as 2021/09/08 20:00:00.

Change datetime format

So how about changing datetime format ?

Change delimiter from / to -.

Then select table again.

Success!

Not format YYYY/MM/DD hh:mm:ss, but format YYYY-MM-DD hh:mm:ss could be converted to datetime.

Conclusion

Today I introduced about "DateTime convert error between spreadsheet and BigQuery".

The root cause of datetime conversion error between spreadsheet and BigQuery is datetime format.

Datetime value of spreadsheet can't be converted to BigQuery datetime value.

Error example

So add ' to change it to text. Then we can select it in BigQuery table.

Success example

I understood. But I'm not convinced.

I see. Spredsheet's datetime should be acceptable in BigQuery because they are both on Google service.

There are some other articles about BigQuey.

If you interested in them, please read them.