script insert SQL Server 2005 from SQL Server 2014 Express

Question

I have a database in SQL Server 2014 Express, and I want to import it to SQL Server 2005.

I have generated the scripts to generate the database and to insert the data. All the script generated to be compatible with SQL Server 2005.

I have problems when inserting datetime. I get this error:

Msg 8115, Level 16, State 8, Line 2
Arithmetic overflow error converting numeric to data type Numeric.

The datetime exported from SQL Server 2014 is:

CAST(N'2018-10-22 00:00:00.000' AS DateTime)

YYYY-DD-MM works if I edited the query, but it’s a big database

Does anyone have an idea how to solve this?

Answer 1

You can change the client date format when you do the INSERT.

SET DATEFORMAT ymd

Run this as the first line in the script that inserts the data, and this will tell the client to interpret the date correctly as YEAR-MONTH-DATE as it is formatted in the script.