Loading ...
Sorry, an error occurred while loading the content.

Dates imported from excel.

Expand Messages
  • duellergr
    While importing from an excel file, I get the error : Row XX failed to insert, error: Conversion failed when converting datetime from character string. The
    Message 1 of 2 , Jul 1, 2007
    View Source
    • 0 Attachment
      While importing from an excel file, I get the error :
      Row XX failed to insert, error: Conversion failed when converting
      datetime from character string.
      The problem occurs when converting from a column that its category in
      excel is date and the destination column has datetime type. All the
      dates in my excel are dd/mm/yyyy format.
    • Robert Baker
      Hi, What is the date time format of the database? Are the values stored as dates or text in the Excel file? Check by changing the cell format to a number
      Message 2 of 2 , Jul 2, 2007
      View Source
      • 0 Attachment

        Hi,

          What is the date time format of the database?  Are the values stored as dates or text in the Excel file?  Check by changing the cell format to a number format, date values will appear as decimal numbers when this is done. 

          I do two steps when inserting dates.  I run “SET DATEFORMAT YMD” on the connection to let SQL server know my string date format.  I then format date times to a string for the insert using a YYYY-MM-DD format.

         

          If all of the above items check out then to gather further information before running Import turn on SQL Trace from the Tools/SQL Trace menu.  I recommend sending output to a File.  Run import, get the error and end import.  Turn off SQL Trace.  Then you can load the trace file (Tools/SQL Trace/Show Logfile) and send me a copy directly and I will investigate some more.  A support bundle will also prompt to include the trace file if it exists and you can send me that instead if you wish.

         

        Thank you,

        Robert

        robert.baker@...

         

      Your message has been successfully submitted and would be delivered to recipients shortly.