Error "[Microsoft][SQL Server Native Client 11.0][SQL Server]The datediff function resulted in an overflow."

KNOWLEDGE BASE

Error "[Microsoft][SQL Server Native Client 11.0][SQL Server]The datediff function resulted in an overflow."

Published: 08 Feb 2017
Last Modified Date: 09 Feb 2017

Issue

The following error occurs when using a DATEDIFF function in a workbook connected live to SQL Server:

"[Microsoft][SQL Server Native Client 11.0][SQL Server]The datediff function resulted in an overflow. The number of dateparts separating two date/time instances is too large. Try to use datediff with a less precise datepart"

Environment

Tableau Desktop
Microsoft SQL Server

Resolution

Option 1:

Use an extract instead of a live connection. For more information, see Extract Your Data.

Option 2:

If a live connection is necessary change the 'datepart' value to one that does not surpass the limits set by Microsoft SQL Server.
For more information on the maximum values for dateparts in Microsoft SQL Server, see DATEDIFF (Transact-SQL) at Microsoft Developer Network.

Cause

This error message originates in Microsoft SQL Server and occurs when DATEDIFF results in Tableau Desktop surpass a maximum allowable value in SQL Server.