Problem inputting DateTime from text using I&ED
-
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?
-
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) -
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.
-
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