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".

Prepare data

First, we have to prepare data.

Today we use spreadsheet.

STEP

Prepare spreadsheet data

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

Then you can see file options.

So click "Google (Spread)Sheet".

After that input data like below.

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

2021/09/08 20:00:00

STEP

Create 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.

So detail window comes from right, and input details.

Settings are below.

parameter	value
Source	Drive
Select Drive URL	(URL of the spreadsheet)
Schema	–
Name	(anything is OK)
Type	DATETIME
Advanced options	–
Header rows to skip	1

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.

Google スプレッドシートで複雑な CSV ファイルを BigQuery に読み込む | Google Cloud Blog

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.

How can we modify it ?

Test : 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.

Then try to select the data on BigQuery.

Ooooops! It shows error again!

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.

Test : Change datetime format

So how about changing datetime format ?

Change delimiter from / to -like below.

2021–09–08 20:00:00

Then select table again.

OK! It seems 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.

So add ' to change it to text. And change delimiter from / to - .
Then we can select it in BigQuery table.

I understood. But I'm not convinced.

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