Convert Datetime to date field to pass into Google BigQuery

Question

I am uploading data into BigQuery Cloud by my c# Application. I have column in BigQuery table 'ForDate' with datatype 'Date'.

But in c# datatype is 'datetime'(As date datatype not supported in c#) I am getting Below error on uploading data to bigquery: {Invalid date: '2017-01-02T00:00:00' Field: ForDate; Value: 2017-01-02T00:00:00}

What could be the workaround to upload date with date part only and ignore time part from it?

score 4 · Answer 1 · edited Jul 02 '18 at 01:43

4

This is working for me:

string currentdatetime = DateTime.Now.ToUniversalTime().ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss'.'fff'Z'");

From:

How can I format DateTime to web UTC format?

edited Jul 02 '18 at 01:43

CertainPerformance

356,069
52
309
320

answered Jul 02 '18 at 01:23

Dinesh Nisshanka

51
5

This is not working for me. I've looked at: https://googleapis.github.io/google-cloud-dotnet/docs/Google.Cloud.BigQuery.V2/ and it has a similar solution, BUT if I try to set date to: 'DateTime.UtcNow' its not working neither by ToUniversalTime() or by the StringFormat, any thoughts ? – Guy P Sep 12 '19 at 14:11

score 0 · Answer 2 · answered Jan 09 '18 at 06:38

0

Import said column as a string, parse in BigQuery to a timestamp value later.

SELECT TIMESTAMP('2017-01-02T00:00:00')

answered Jan 09 '18 at 06:38

Felipe Hoffa

54,922
16
151
325

score 0 · Answer 3 · answered Mar 26 '19 at 08:18

0

The date field in the BigQuery schema should be marked as a TimeStamp not as a DateTime field. Then you can simply insert the row with:

bqr.Add(attribute, date.ToUniversalTime());

answered Mar 26 '19 at 08:18

cobus swart

176
1
5

score 0 · Answer 4 · answered Sep 15 '19 at 09:32

I know its been a while since this question was asked, but for me none of the answers was correct. It seems that the BigQuery NuGet doesn't like date times with specified kind (e.g UTC), and ToUniversalTime() produces an UTC kind.

The only trick that worked for me was to create a new instance of DateTime with none (Unspecified) kind.

var now = DateTime.UtcNow;
var dt = new DateTime(now.Year, now.Month, now.Day, now.Hour, now.Minute, now.Second, now.Millisecond);

chemark · Answer 5 · 2021-09-22T19:06:23.103

0

You can use:

BigQueryDateTimeExtensions.AsBigQueryDate(DateTime.UtcNow)

From: Class BigQueryDateTimeExtensions

For DateTime use:

DateTime.UtcNow.ToString("s")

edited Sep 22 '21 at 19:06

answered Sep 22 '21 at 18:47

chemark

1,181
1
13
19

Convert Datetime to date field to pass into Google BigQuery

5 Answers5