We have Data Engineers working in BigQuery (SQL) and others working with dataprep. There is a constraint to set future dates to '3000-12-31T12:00:00'. I couldn't tell dataprep to find this a valid date. Is there a way to set DATETIME(3000,12,31,12,0,0)

[Trifacta_Alumni]

Hi @Armin Meyer?,

The date/time datatype in Dataprep supports dates from January 1, 1400, to December 31, 2599. Any dates outside of this range are treated as invalid. It does look like the future date that your team needs to insert falls outside of the valid range recognized by Dataprep.

In order to insert this date into your output BigQuery table, I would recommend setting the future date cells to "NULL". You can then configure Dataprep to run a post-job SQL statement that will update the NULL values to include your desired future date. Here's an example of a post-job SQL statement that could accomplish this:

UPDATE dataset.table SET datecolumn = "3000-12-31T12:00:00" WHERE datecolumn is null

Please let me know if this works!

[ameyer]

Hi @Connor Carreras? ,

thanks for your helpful answer. Good to know which range of dates is defined as valid.

We will wether discuss our teams definition of "future" ;) or try the post-job.

Cheers

Armin