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

URGENT - Date conversion

Expand Messages
  • Sagar Bhargava
    I have a txt file that gets populated by DOS FORFILES command and inserts Filename, FileDate and FileTime. FileDate is in DD/MM/YYYY format. When I try to bulk
    Message 1 of 1 , May 29 9:17 PM
    • 0 Attachment
      I have a txt file that gets populated by DOS FORFILES command and inserts
      Filename, FileDate and FileTime. FileDate is in DD/MM/YYYY format. When I
      try to bulk insert this file in a table with 3 columns (A, DD, Time), I get
      this error message. DD Column is of DateTime data type.

      Msg 4864, Level 16, State 1, Line 2
      Bulk load data conversion error (type mismatch or invalid character for the
      specified codepage) for row 16, column 2 (DD).

      23/12/2008 results in a error message but 01/08/2008 inserts fine. So, I
      understand that SQL Server is accepting date in MM/DD/YYYY.

      How do I insert this data from the textpad to the Table ?

      The SQL that I have been using looks like this :

      create table #temp_table2(PID nvarchar(50), DD *nvarchar(50)*, TT
      nvarchar(50)) - - - - - I tried changing nvarchar(50) to datetime but
      gives the above error message
      BULK INSERT #temp_table2 FROM 'E:\RemoveProjectFromDpm\lastactivate.txt'
      WITH (
      DATAFILETYPE = 'char',
      FIRSTROW = 1,
      FIELDTERMINATOR = ',',
      ROWTERMINATOR = '\n',
      keepnulls
      )

      insert into zzzzz.dbo.temp88 (PID, DD, TT)
      select PID, convert(datetime,DD,101), TT
      from #temp_table2

      Executing this above SQL results in this error message :
      Msg 8152, Level 16, State 13, Line 1
      String or binary data would be truncated.
      The statement has been terminated.


      This is kinda urgent, so any help would be much appreciated

      Thanks and regards,
      Sagar


      --
      " It's Not what I am within; It's what I do that defines ME ! "


      [Non-text portions of this message have been removed]
    Your message has been successfully submitted and would be delivered to recipients shortly.