Error executing TSQL insert statement

Question

One of our users made an insert statement on the development server. The insert worked fine. However, when the same insert statement was executed on the production server, it found the error below:

Error: .Net SqlClient Data Provider Error Message: Converting a char data type for a date and time data type led to an out of range datetime value. Application completed.

The insert statement is below:

Insert into tbl_SeatWave_customers (Title ,FirstName ,Lastname ,EmailAddress ,HomeTelephone ,Address1 ,Address2 ,Address3 ,Town ,County ,Postcode ,BuyerOrSeller ,OrderID ,ListingID ,BestCallDateTimeFrom ,bestcalldatetimeto ,FAQAsked ,Comments ,CallOutcome ,Spare1 ,Spare2 ) Values('Mr' ,'Darren' ,'Piper' ,' [e-mail] ' ,'07825758328' ,'7 RUSSELL ROAD' ,'' ,'' ,'' ,'CLWYD' ,'LL18 3BS' ,'Other' ,'' ,'19/06/2009' ,'12:00' ,'' ,'Callers tickets have not yet arrived.' ,'Resolved' ,'Yes' ,'' ) 

The tbl_SeatWave_customers table has the following structure:

 COLUMN_NAME DATA_TYPE COLUMN_DEFAULT NUID bigint NULL CallDateTime datetime (getdate()) Title nvarchar NULL FirstName nvarchar NULL LastName nvarchar NULL EmailAddress nvarchar NULL HomeTelephone nvarchar NULL MobileTelephone nvarchar NULL WorkTelephone nvarchar NULL Address1 nvarchar NULL Address2 nvarchar NULL Address3 nvarchar NULL Address4 nvarchar NULL Town nvarchar NULL County nvarchar NULL Postcode nvarchar NULL BuyerOrSeller nvarchar NULL OrderID nvarchar NULL ListingID nvarchar NULL BestCallDateTimeFrom datetime NULL BestCallDateTimeTo datetime NULL FAQAsked nvarchar NULL Comments nvarchar NULL Spare1 nvarchar NULL Spare2 nvarchar NULL Spare3 nvarchar NULL Spare4 nvarchar NULL Spare5 nvarchar NULL CallOutcome nvarchar NULL 

My question is, what is the cause of this error and what is the resolution to fix it?

Answer 1

Your date literally '19 / 06/2009 'can be interpreted as style 101, USA, mm / dd / yyyy or style 103, British / French, dd / mm / yyyy. 19 is not a valid "numeric month", so I assume that your development server is set to British / French (06/19/2009 => Fri, June 19, 2009) and your production server to us styles / formats (19 / 06/2009 => error).

http://msdn.microsoft.com/en-us/library/ms180878.aspx#UsingDateandTimeFormats says:

String literal formats affect the presentation of data in applications to users, but not the main format for storing integers in SQL Server. However, SQL Server can interpret a string literal date value entered by an application or user for storage or by a date function on different dates. The interpretation depends on a combination of the string literal format, data type, and runtime of SET DATEFORMAT, SET LANGUAGE, and default language settings .

and

We recommend that you use date and time formats that are DATEFORMAT independent and multilingual. The ISO 8601, "1998-02-23T14: 23: 05" and "1998-02-23T14: 23: 05-08: 00" standards are the only formats that are an international standard. They are not DATEFORMAT or the standard login language and are multilingual.

Answer 2

My guess would be either internationalization ( 06/19/2009 vs 06/19/2009 vs 2009 06 19 coming from a client computer and / or a configured server differently - a hint, a fairly common layout would be "2009/06/19" ), or simply because you may have used the date incorrectly, and now the 12th, i.e. previously it was believed that it was a boom on October 6, November 6, December 6.

Of course, if you use parameters (instead of strings), you do not have this problem. The same with decimal places - is 123,456 the same as 123456? or 123 and almost half?

Answer 3

The problem is `` 06/19/2009 ' which is specified in DD / MM / YYYY but your SQL Server instance expects it as MM / DD / YYYY`.

Answer 4

DateFormats mm / dd / yyyy compared to dd / mm / yyyy depends on the language setting for logging in, which is used to connect to the server. Read more here:

Setting the default date format for SQL Server