# Getting error Invalid Date while using GX with Bigquery

**URL:** <https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397>\
**Category:** GX Core Support\
**Tags:** help-wanted\
**Created:** [September 15, 2023, 7:51pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397 "2023-09-15T19:51:11Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Ravindra](https://avatars.discourse-cdn.com/v4/letter/r/35a633/32.png) [@Ravindra](https://discourse.greatexpectations.io/u/Ravindra)\
**Post date:** [September 15, 2023, 7:51pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/1 "2023-09-15T19:51:12Z")

</div>

I followed this [How to create an Expectation Suite with the Onboarding Data Assistant | Great Expectations](https://docs.greatexpectations.io/docs/0.16.16/guides/expectations/data_assistants/how_to_create_an_expectation_suite_with_the_onboarding_data_assistant) and Tried to run onboarding data assitant script except 7 cases all other fails and the exception which I get is (google.api\_core.exceptions.BadRequest: 400 Invalid date: ‘2023-09-12T00:00:00’) and sqlalchemy points to [Error Messages — SQLAlchemy 1.4 Documentation](https://sqlalche.me/e/14/4xp6), I tried multiple things downgrading dependencies versions etc but no luck, can anyone please help me here I would really appreciate

---

<div class="post-metadata">

**Author:** ![rachel.house](https://yyz1.discourse-cdn.com/flex031/user_avatar/discourse.greatexpectations.io/rachel.house/32/236_2.png) [@rachel.house](https://discourse.greatexpectations.io/u/rachel.house)\
**Post date:** [September 18, 2023, 2:11pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/2 "2023-09-18T14:11:17Z")

</div>

Hi @Ravindra! I noticed that you referenced the Onboarding Data Assistant tutorial from an older version of GX - what version of GX are you currently running?

If you’re able to upgrade to the latest version, does the error still persist?

---

<div class="post-metadata">

**Author:** ![Ravindra](https://avatars.discourse-cdn.com/v4/letter/r/35a633/32.png) [@Ravindra](https://discourse.greatexpectations.io/u/Ravindra)\
**Post date:** [September 18, 2023, 3:01pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/3 "2023-09-18T15:01:28Z")

</div>

Hi @rachel.house I am using the latest version 0.17.16 following this example : [Create an Expectation Suite with the Onboarding Data Assistant | Great Expectations](https://docs.greatexpectations.io/docs/guides/expectations/data_assistants/how_to_create_an_expectation_suite_with_the_onboarding_data_assistant).  
Some how Great Expectation is treating Date field in Bigquery as datetime field the parameters which are sent with query is datetime instead of date for a date field due to which around 67 expectations fails.

---

<div class="post-metadata">

**Author:** ![rachel.house](https://yyz1.discourse-cdn.com/flex031/user_avatar/discourse.greatexpectations.io/rachel.house/32/236_2.png) [@rachel.house](https://discourse.greatexpectations.io/u/rachel.house)\
**Post date:** [September 18, 2023, 3:35pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/4 "2023-09-18T15:35:55Z")

</div>

Hi @Ravindra, thanks for the additional context! I have a couple more questions to help make sure I understand the behavior you’re seeing. (I’m still relatively new to GX as well, thanks for your patience).

You’re connecting to a BigQuery database and then running the Onboarding Data Assistant.

- Does the `run(...)` method fail with the `google.api_core.exceptions.BadRequest: 400 Invalid date` Exception that you included in your original post
- Or does the `run(...)` method succeed, and then Expectations from the generated Expectation Suite fail with date/datetime data type errors?

---

<div class="post-metadata">

**Author:** ![Ravindra](https://avatars.discourse-cdn.com/v4/letter/r/35a633/32.png) [@Ravindra](https://discourse.greatexpectations.io/u/Ravindra)\
**Post date:** [September 18, 2023, 3:45pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/5 "2023-09-18T15:45:26Z")

</div>

Hi @rachel.house this is the exception: google.api\_core.exceptions.BadRequest: 400 Invalid date: ‘2023-09-12T00:00:00’ ,  
I do checkpoint.run() which executes , and then I do 1.  
context.view\_validation\_result(checkpoint\_result)  
which opens the html on browser there I see this exception

---

<div class="post-metadata">

**Author:** ![rachel.house](https://yyz1.discourse-cdn.com/flex031/user_avatar/discourse.greatexpectations.io/rachel.house/32/236_2.png) [@rachel.house](https://discourse.greatexpectations.io/u/rachel.house)\
**Post date:** [September 18, 2023, 5:07pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/6 "2023-09-18T17:07:34Z")

</div>

Hey @Ravindra, can you please share the code you’re using that results in the exception, so that we can try to reproduce the error? Thanks!

---

<div class="post-metadata">

**Author:** ![Ravindra](https://avatars.discourse-cdn.com/v4/letter/r/35a633/32.png) [@Ravindra](https://discourse.greatexpectations.io/u/Ravindra)\
**Post date:** [September 18, 2023, 5:39pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/7 "2023-09-18T17:39:10Z")

</div>

Sure @rachel.house, please find the code below:

```auto
import great_expectations as gx
from great_expectations.exceptions import DataContextError

context = gx.get_context()
expectation_suite_name = "Onboarding_suite"
try:
    suite = context.get_expectation_suite(expectation_suite_name=expectation_suite_name)
    print(
        f'Loaded ExpectationSuite "{suite.expectation_suite_name}" containing {len(suite.expectations)} expectations.'
    )
except DataContextError:
    suite = context.add_expectation_suite(expectation_suite_name=expectation_suite_name)
    print(f'Created ExpectationSuite "{suite.expectation_suite_name}".')

datasource = context.sources.add_or_update_sql(
    name="my_bigquery_datasource",
    connection_string="bigquery://project-dev", #changed_my_actaull_project_name
    create_temp_table=False,
)
table_asset = datasource.add_table_asset(
    name="mytable", table_name="dataset.tablename" #changed my actaual dataset/tablename
)
request = table_asset.build_batch_request()
context.add_or_update_expectation_suite(expectation_suite_name=expectation_suite_name)
validator = context.get_validator(
    batch_request=request, expectation_suite_name=expectation_suite_name
)
data_assistant_result = context.assistants.onboarding.run(
    validator=validator,
    exclude_column_names=[],
)
validator.expectation_suite = data_assistant_result.get_expectation_suite(
    expectation_suite_name=expectation_suite_name
)
validator.save_expectation_suite(discard_failed_expectations=False)
checkpoint = context.add_or_update_checkpoint(
    name=expectation_suite_name,
    validator=validator,
)
checkpoint_result = checkpoint.run()

context.view_validation_result(checkpoint_result)

```

---

<div class="post-metadata">

**Author:** ![rachel.house](https://yyz1.discourse-cdn.com/flex031/user_avatar/discourse.greatexpectations.io/rachel.house/32/236_2.png) [@rachel.house](https://discourse.greatexpectations.io/u/rachel.house)\
**Post date:** [September 18, 2023, 8:56pm UTC](https://discourse.greatexpectations.io/t/getting-error-invalid-date-while-using-gx-with-bigquery/1397/8 "2023-09-18T20:56:36Z")

</div>

Thanks, @Ravindra! So I don’t see any problems with your code - as a next step, I’d suggest trying to isolate why and where this issue is happening by simplifying what your code is doing.

Instead of using the Onboarding Data Assistant, can you try instead creating a simple Expectation Suite with just a few Expectations that validates the specific date/datetime columns in your BigQuery table?

If you are able to reproduce the error with the simplified code, would you then be able to share:

- your simplified code
- a sample of your BigQuery data, including the column data types

If your code succeeds without using the Onboarding Data Assistant, then we’ll dig in there next.