none
Changeing from old ODBC to newer version gives error "Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding" RRS feed

  • Question

  • Hi!

    I've a Windows SQL Server 2008R2. On this server I've a ODBC connection and when i try to change the driver to a newer one (SQL 11, 17) It doesnt add the data to the database and givies me the following error in the logs "Datetime field overflow. Fractional second precision exceeds the scale specified in the parameter binding", is this something that I can solve myself in the database or is this something that need to be solved in the application using the odbc connection?

    Sunday, February 18, 2018 10:27 PM

All replies

  • http://rightondevelopment.blogspot.ca/2009/10/sql-server-native-client-100-datetime.html

    A Fan of SSIS, SSRS and SSAS

    Sunday, February 18, 2018 10:51 PM
  • What ODBC driver were you using previously? Is the problem column data type datetime or datetime2?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Sunday, February 18, 2018 11:29 PM
    Moderator
  • What ODBC driver were you using previously? Is the problem column data type datetime or datetime2?


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com

    Hi!

    The driver that works is: "SQL SERVER 6.01.7601.17514"

    How can I see which column it it complaining about?
    Without having too much knowledge I think it is datetime (not datetime2)

    //BR
    Patrik


    • Edited by Patrik.L Monday, February 19, 2018 6:34 AM
    Monday, February 19, 2018 6:23 AM
  • Looks like issue is with datetime2. The precision set seems to be inline with datetime and value you're getting (source column) looks like a datetime2 one

    As per this

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements

    You should be using SQL_TYPE_TIMESTAMP /  SQL_TIMESTAMP with the relevant scale value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Monday, February 19, 2018 6:29 AM
  • Looks like issue is with datetime2. The precision set seems to be inline with datetime and value you're getting (source column) looks like a datetime2 one

    As per this

    https://docs.microsoft.com/en-us/sql/relational-databases/native-client-odbc-date-time/data-type-support-for-odbc-date-and-time-improvements

    You should be using SQL_TYPE_TIMESTAMP /  SQL_TIMESTAMP with the relevant scale value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page


    Without having too much knowledge I think the columns is datetime (not datetime2)

    //BR
    Patrik


    Monday, February 19, 2018 7:59 AM
  • Also here are some snipps from the timestamps trying to be written:

    OrigTransactionTime=2018-2-16 15:32:42.0
    Transaction Time=2018-2-16 15:32:46.719000000
    Entry_Time=2018-2-16 15:32:46.379000000
    Exit_Time=2018-2-16 15:32:46.383000000
    Re_Entry_Time=2018-2-16 15:32:46.709000000

    And I checked thoose columns and they seems to use "datetime" as "datatype" and "Systemtype" and also they use "Numeric scale 3" (if that is the same as scale?) (I found these under properties of the column)

    Monday, February 19, 2018 8:10 AM
  • values like 2018-2-16 15:32:46.719000000 are not datetime but scale looks more in line with datetime2 range

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, February 19, 2018 8:28 AM
  • The driver that works is: "SQL SERVER 6.01.7601.17514"

    How can I see which column it it complaining about?

    I suspected as much. I don't think there is anything you can do on the server side as the error occurs on the client before the request is sent to SQL Server. The driver is complaining about the parameter, not a column. The latest ODBC driver you can use without code changes is the SQL Server 2005 version of SQL Server Native Client.

    The newer ODBC drivers released after SQL 2008 provide sub-millisecond fractional precision for the SQL_TYPE_TIMESTAMP ODBC data type in order to support the newer SQL Server datetime types. This is a breaking change for older client applications as detailed in https://docs.microsoft.com/en-us/sql/relational-databases/native-client/applications/updating-an-application-from-sql-server-2005-native-client (emphasis mine):

    <Excerpt>
    Prior to SQL Server 2008 Native Client, datetime values were rounded to fit the scale of datetime and smalldatetime columns by SQL Server. SQL Server 2008 Native Client (and later) applies the stricter validation rules that are defined in the ODBC core specification for fractional seconds. If a parameter value cannot be converted to the SQL type by using the scale specified or implied by the client binding without truncation of trailing digits, an error is returned.
    </Excerpt>

    I think the application change is just a matter of specifying a scale of 3.


    Dan Guzman, Data Platform MVP, http://www.dbdelta.com



    Monday, February 19, 2018 11:29 AM
    Moderator