DataSource.Error: Microsoft SQL: Adding a value to a 'datetime2' column caused an overflow.

[AntonH]

Hi, I've been trying to find a solution to this for quite a while but haven't succeeded, so I'm trying here 🙂

I'm new to the Power BI world, so sorry if it's an easy fix. But I'm getting an error message when I try to convert my date table to date format. It's in PowerQuery and I'm getting the data directly from an SQL server. How can I make it recognize that it's a date?

Error message:

DataSource.Error: Microsoft SQL: Adding a value to a 'datetime2' column caused an overflow.
Details:
DataSourceKind=SQL
DataSourcePath='server name'
Message=Adding a value to a 'datetime2' column caused an overflow.
ErrorCode=-2146232060
Number=517
Class=16

[rohitreddy1311]

i was having the same issue while trying out adventureworksDW2022 dataset and i had no much knowledge of sql editing or it wasnt possible to filter out the column as the dataset rows are huge. 

i tried power query editor --> Transform--> Format--> trim/clean --> now change the date type to date . 

hope this helps somone new at power bi 

[rajendraongole1 · Accepted solution]

Hi @AntonH -Go to the query editor in Power BI (Power Query).
Look at the column that holds the date values and see if there are any invalid dates eg: 0000-00-00, or dates far in the future or past that could cause this issue.
Replace or Filter Out Invalid Dates:In Power Query, you can use the Replace Values function to replace invalid date values with null or a valid placeholder date.
Alternatively, you can filter out rows where the date is invalid using the Filter Rows option.
Replace values that may cause overflow errors (like 0000-00-00) with a more manageable default date, example:  2000-01-01

check the column header of date and data type choose date/datetime data type

Hope this prevent the overflow errors.