locked
Problem to insert rows with DateTime conversion error RRS feed

  • Question

  • The insert query form profiler is:

    declare @p13 int
    set @p13=0
    exec [Contract_insert] @P_1=3603,@P_2=1,@P_3='2005-08-01 22:37:21.2570000 ',@P_4='2007-04-05 20:57:44.9000000 ',@P_5=164,@P_6='2007-09-01 00:00:00',@P_7=NULL,@P_8=NULL,@P_9=6039,@P_10=NULL,@P_12=0,@P_13=0,@sync_row_count=@p13 output
    select @p13

    I got this error message when run the above query in query designer.
    "Error converting data type varchar to datetime."

    It seems datetime data passed to insert command with 7 last digit caused problem. If I delete last 3 zero, the query runs successfully. Does anyone know how to solve this problem?

    Thanks,

    jshaqd
    • Moved by Max Wang_1983 Thursday, April 21, 2011 10:28 PM forum consolidation (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Monday, November 30, 2009 3:55 PM

Answers

  • Where are you getting this '2005-08-01 22:37:21.2570000' value from?
    Also I encourage you to please use the latest Sync Framework v2 RTM bits.

    From the SQL Server 2008 books online, you can see that the precision is 7 digits for datetime2 while 3 for datetime.

    Data type Output

    time

    12:35:29. 1234567

    date

    2007-05-08

    smalldatetime

    2007-05-08 12:35:00

    datetime

    2007-05-08 12:35:29.123

    datetime2

    2007-05-08 12:35:29. 1234567

    datetimeoffset

    2007-05-08 12:35:29.1234567 +12:15


    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 1, 2009 9:57 PM

All replies

  • Can you check if you have datetime or datetime2 defined.
    Maybe you need to define them as a datetime2?

    Also what version of Sync framework bits are you using?
    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 1, 2009 1:20 AM
  • The base table is using datetime.

    This happens occasionally. Apply changes failed in the first time. When I sync the tables again. All synced well.

    I am using sync framework v2 cpt1.

    Thanks,

    -jshaqd


    Tuesday, December 1, 2009 5:15 PM
  • Where are you getting this '2005-08-01 22:37:21.2570000' value from?
    Also I encourage you to please use the latest Sync Framework v2 RTM bits.

    From the SQL Server 2008 books online, you can see that the precision is 7 digits for datetime2 while 3 for datetime.

    Data type Output

    time

    12:35:29. 1234567

    date

    2007-05-08

    smalldatetime

    2007-05-08 12:35:00

    datetime

    2007-05-08 12:35:29.123

    datetime2

    2007-05-08 12:35:29. 1234567

    datetimeoffset

    2007-05-08 12:35:29.1234567 +12:15


    This posting is provided AS IS with no warranties, and confers no rights
    Tuesday, December 1, 2009 9:57 PM