KNOWLEDGE BASE

Error: "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value."


Published: 05 Apr 2016
Last Modified Date: 19 Jan 2017

Issue

When you try to use a date field formatted as datetime2 from MS SQL Server, the following error might occur:
The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value.

Environment

  • Tableau Desktop 9.2.9 and earlier versions
  • MS SQL Server 2008 or later

Resolution

Option 1: 

Upgrade to Tableau Desktop 9.2.10 or a later version. For more information, see Upgrade Tableau Desktop

Option 2:

  1. Cast the field as a string (nvarchar) in the database.
  2. In Tableau Desktop, navigate to Analysis > Create Calculated Field and enter a calculation like the following to convert the field back to a date:
    DATE([date field])
    or
    DATETIME([date field])
Invalid date range values will show as null and the error will not be returned.

Cause

This behavior is related to a known issue with ID 313098 which has been corrected in more recent versions. 

The error occurs for two reasons:
  • Values exist in that field's domain that are prior to Jan 1, 1753.  Earlier versions of Tableau Desktop use datetime, which only supports date values after 1/1/1753
  • The ability to use datetime2 fields is not built into earlier versions of Tableau Desktop 
Did this article resolve the issue?