Type mismatch error on Date field during DTS import

[RobColborne]

Hi

A BCP import that worked on a SQL server 6.5 has been moves to SQL 2000 using DTS but is getting errors.

The original task made use of text files with an accompanying Format file. In SQL 2000, The Bulk Insert task has been set up. However, when the process is run an error is generated which is:

Bulk insert data conversion error (type mismatch)

The date & time in the txt file stores the date in British format, dd/mm/yyyy, e.g. 17/09/2004 00:01

Looking at where the errors are occuring it is when the day is greater than 12. Therefore, I believe that the SQL server is expecting the dates in US format of mm/dd/yyyy.

I thought that using the Set Language 'British English' would sort this but it has not.

What do I need to do to get this to work correctly as it did on 6.5.

Thanks

Rob

 

[SQLBill]

First, when the SQL Server 2000 was built it probably was set to the US collation.

To solve the issue, you could change the collation for the server. Or add

SET DATEFORMAT DMY

to your DTS script. DATEFORMAT tells SQL Server how to interpret the incoming datetime values.

Disclaimer: I don't use BCP so I can't advise you on adding it to your script.

Refer to the BOL for more information on SET DATEFORMAT.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[RobColborne]

Thanks SQLBill, that seems to have worked a treat!