I want to import some data into my MS SQL server thought
DTS.

I have all the filed import ok apart from the date feild
as it is in a string format IE 20040408 which relates
back to YYYYDDMM. how can i convert this date into a
format a format that can be imported into my TimeDate
field?

Here is the SQL that i have created for the import so far.

CREATE TABLE [IPS].[dbo].[RevByyear] (
[YEAR_NBR] varchar (4) NULL,
[RES_AGENT_CD] varchar (30) NULL,
[INVOICE_AMT] money NULL,
[RANK_NBR] int NULL,
[PROCESS_DAT] datetime NULL
)

I get a message when i import saying
TransformCopy 'DirectCopyXForm' conversion error:
Convertion invaild for datatypes on coloum pair 5
(sourcecolumn 'Col005'(DBTYPE_STR), destination
column 'PROCESS_DAT' (DBTIMESTAMP)).

so i am assuming that i can just import the date from my
test file straight in without using a convert command
somewhere. here are the first 2 line of the TXT file.

2004,MMUSE,205590.64,1,20040409,20040408
2004,MDIKE,162266.26,2,20040409,20040408

So i dont think their can be a problem with the data that
i am importing, is their?

Willa

Hi Willa,

I have a workaround for the issue related to importing the date that is in
the string format in the text file to the SQL table.

Create the table without the datetime field, PROCESS_DAT.
Create a varchar(8) field, name it as PROCESS_DAT1.
Import the data from the text file to the table using the dts import
wizard, updating the Process_Dat1 field with the string values of the date.
Modify the structure of the table and add the actual field, PROCESS_DAT,
that would hold the date, and the datatype can be datetime.

After that you need to run the following Query that would convert the
varchar value to the date and update the PROCESS_DAT field.

update revbyyear set process_dat =convert(datetime,left(process_dat1,4) +
'/' + substring(process_dat1,7,2)+ '/' + substring(process_dat1,5,2))


HTH
Ashish
This posting is provided "AS IS" with no warranties, and confers no rights.

You file def does not match your table def.

That said when moving dates from a character field to a datetime field I
like to use this method

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Willa" <will (AT) ncl (DOT) com> wrote