Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

BigQuery Sink | Datetime parsing fails when subsecond decimal places are greater than 6.

Description

We have MSSQL Server as a source and BigQuery as a sink. MSSQL Server has datetime2 type. This type supports seven subsecond decimal places. On the other hand, BigQuery only supports six. Because of this, we are facing the following error:

Invalid datetime string "2022-01-30 11:11:11.1111111"

 

My suggestion is to trim the timestamp in the BQ Sink. Maybe, there is a better alternative.

Release Notes

None

Activity

Show:

Danish AmjadJune 18, 2024 at 8:38 PM

Quick production of the issue in BQ:

CREATE TABLE `my-project-1290.danish_temp.temp_ts_precision_test` ( id STRING, f_datetime DATETIME, f_timestamp TIMESTAMP, f_time TIME ); INSERT INTO `my-project-1290.danish_temp.temp_ts_precision_test` SELECT "1", DATETIME("2022-01-30 11:11:11.1111111"), TIMESTAMP("2022-01-30 11:11:11.2222222"), PARSE_TIME('%H:%M:%E*S',"11:11:11.3333333");

As soon as one reduces the decimal places to 6. It works.

Pinned fields
Click on the next to a field label to start pinning.

Details

Assignee

Reporter

Reviewer

Components

Priority

Created June 18, 2024 at 8:31 PM
Updated June 19, 2024 at 8:59 AM