SQL Server Host Migration Datetime Problem

I'm trying to move a SQL Server database from webfusion to heartinternet. 开发者_开发知识库It was easy enough to export the database and table structure but the insert statements don't work.

I get the following error:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Now this is one example of an insert statement:

INSERT [ECL_TRANSACTION] ([PUBLIC_USER_CODE], [TIMESTAMP], [TRANSACTION_TYPE], [TRANSACTION_DESCRIPTION]) 
  VALUES (-9, '16/03/2011 06:30:19', 'A', 'Login Success')

The problem is that datetime field [TIMESTAMP] will not accept 16/03/2011 06:30:19

I've read that you can use a cast or convert on each field to sort this but I've got 3000 insert statements and was wondering is there a way to apply some rule to all datetime fields to accept DD/MM/YYY HH:MM:SS format.

Any advice would be much appreciated, cheers.

Try:

SET DATEFORMAT dmy

in your batches

You could try something along the lines of SET DATEFORMAT dmy at the start of your script.

EDIT

The following link also suggests a command enabling you to change the settings on the server instance, if you have that level of access...

INF: How to Set the Day/Month/Year Date Format in SQL Server