Post reply

Problem inputting DateTime from text using I&ED

  • November 13, 2008 at 5:26 am

    #193780

    I have SQLserver 2008 express and am trying to import data from flat files which include datetime in text string - e.g. dd-mm-yyyy hh:mm:ss.nnn

    If I don't have the fractions of a second - then the data imports sucesfully. However, as soon as I add the fractions (which I must have) I receive the following error message:

    Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data conversion for column "Logged" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".

    (SQL Server Import and Export Wizard)

    I have tried using DT_DATE, DT_STR, DT_DBTIMESTAMP and DT_DBTIMESTAMP2- in each case the destination type is DATETIME. All other options are the defaults.

    Can anyone advise me on the way to go?

  • November 14, 2008 at 7:23 am

    #898571

    I can't be sure, but it might be the "dd-mm-yyyy" portion of the format that's the problem. SQL Server defaults to mm-dd-yyyy, at least here in the US anyway. Can you make that kind of change and give it a try? You might also just need to do a SET DATEFORMAT DMY to get it to work. Try the latter first.

    Steve

    (aka smunson)

    :):):)

    Steve (aka sgmunson) 🙂 🙂 🙂
    Rent Servers for Income (picks and shovels strategy)

  • November 14, 2008 at 2:16 pm

    #898879

    I've finally managed a long way around the problem. I created the table with a text field and a datetime field. I imported the data into the text fields (just over 4 million records), then used a query to update the datetime field - setting it to CONVERT([Date_Txt],121). Very long-winded and not useful when I come to add data to the table rather than this initial table-build.

    Any ideas of how to do it with the I&ED wizzard would be very much apreciated.

  • November 14, 2008 at 2:18 pm

    #898881

    Thanks for the thought. I'm in the UK and so, hopefully, it would recognise the dd-mm-yyyy as well as yyyy-mm-dd.

    I have tried the dd-mm-yyyy hh:mm:ss.nnn - but this didn't work. I've not tried the mm-dd-yyyy - but will.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply