Date values out of bounds causing dataset refresh to be cancelled

[vicngo]

We are getting an error refreshing our PowerBI datasets against our SQL database tables.
   

     "Cancellation occurred when sending or receiving a request"

Tracking down the issue, it looks like some of our date column values are out of range.  Removing the rows that have out of range values unblocks the refresh.

Our SQL tables have datetime2 columns, which in some cases we populated with the .NET DateTime.MinValue value of '01/01/0001'.  This is out of range for a datatype of datetime, but not of datetime2 and we suspect that is causing the issue when refreshing.

Is there a way we can configure our dataset refresh job to allow dates within the datetime2 time range?

[v-shex-msft]

Hi @vicngo,

>>Is there a way we can configure our dataset refresh job to allow dates within the datetime2 time range?

Nope, I don't think it is possible. Perhaps you can consider to change column type to store these unsupported data.

Based on test, power bi default min date is 1/1/1900, available min date is 1/1/0100 when you use date function.

It is impossible to setting date less than 100, if you try to do it, power bi will re-format as 1900+x year.

Regards,

Xiaoxin Sheng