BigQuery on .NET Core - Error on insertion

Question

I'm developing a Google Cloud Function with .NET Core 3.1, and I'm also using BigQuery Library on C#.

When I try to make a insertion like so:

BigQueryResults newResult = client.ExecuteQuery(
        @$"INSERT {table} 
        (deal_id,
         deal_name,
         deal_amount,
         deal_deal_stage_name,
         deal_cf_tipos_de_operao,
         deal_cf_vidas,
         deal_cf_faturamento_estimativa,
         deal_cf_vigncia_mmaaaa,
         deal_cf_agenciamento,
         deal_cf_comisso_mensal,
         deal_cf_valor_por_vida,
         deal_cf_produto,
         deal_cf_benefcios,
         deal_closed_date,
         created_at,
         updated_at)

        VALUES(
            {newRequest.deal_id},
            '{newRequest.deal_name}',
            {Convert.ToInt32(newRequest.deal_amount)},
            '{newRequest.deal_deal_stage_name}',
            '{newRequest.deal_cf_tipos_de_operao}',
            {newRequest.deal_cf_vidas},
            {newRequest.deal_cf_faturamento_estimativa},
            '{newRequest.deal_cf_vigncia_mmaaaa}',
            {newRequest.deal_cf_agenciamento},
            {newRequest.deal_cf_comisso_mensal},
            {newRequest.deal_cf_valor_por_vida},
            '{newRequest.deal_cf_produto}',
            '{newRequest.deal_cf_benefcios}',
            '{newRequest.deal_closed_date}',
            '{newRequest.created_at}',
            '{newRequest.updated_at}')",
        null)
        .ThrowOnAnyError(); 

But I get the following error:

The service bigquery has thrown an exception: Google.GoogleApiException: Google.Apis.Requests.RequestError
    Could not cast literal "01/01/0001 00:00:00" to type DATETIME at [27:37] [400]
    Errors [
        Message[Could not cast literal "01/01/0001 00:00:00" to type DATETIME at [27:37]] 
        Location[q - parameter] Reason[invalidQuery] Domain[global]
    ]

What I'm not understanding is that, the variables I use to store the date values are already of DateTime, like for example, newRequest.deal_closed_date. Maybe the format is wrong?

What can I do to solve this?

Thanks!

Answer 1

Error occured because you are inserting data in string to a column with DATETIME data type in BigQuery.

Convert your data first from String to DateTime data type before inserting into BigQuery. You can do this by using the DateTime.Parse() method:

DateTime dateTime = DateTime.Parse(dateTimeString);