Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
AntonH
New Member

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

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

1 ACCEPTED SOLUTION
rajendraongole1
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

1 REPLY 1
rajendraongole1
Super User
Super User

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.