Error encountered while executing TSQL insert statement

Question

Hi,

One of our user did an insert statement in development server. The insert worked fine. However, when the same insert statement is executed in production server, he encountered the error below:

Error:.Net SqlClient Data Provider
Error Message: The conversion of char data type to a datetime data type resulted in an out-of-range
datetime value.
The statement has been terminated.

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', '[email protected]', '07825758328', '7 RUSSELL ROAD',
 '', '', '', 'CLWYD', 'LL18 3BS', 'Other', '', '', '19/06/2009', '12:00', '',
 'Callers tickets have not yet arrived.', 'Resolved','Yes', '')

The table tbl_SeatWave_customers 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 what is the cause of this error and what is the resolution to fix it?

Answer 1

My guess would be either internationalisation (19/06/2009 vs 06/19/2009 vs 2009 06 19 coming from the client's machine and/or the server configured differently - hint, a fairly common layout would be '2009/06/19'), or simply that you have perhaps been using the date incorrectly, and it is now past the 12th - i.e. previously it thought it was the 6th Oct, 6th Nov, 6th Dec, boom.

Of course, if you use parameters (instead of strings) you don't have this problem. Ditto with decimals - is 123,456 the same as 123456? or 123 and nearly a half?

Answer 2

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

Answer 3

DateFormats mm/dd/yyyy versus dd/mm/yyyy depends on the language setting for the login used to connect to the server. Read here for more:

Setting a standard Date Format for SQL Server

Answer 4

Your date literal '19/06/2009' may be interpreted as style 101,US,mm/dd/yyyy or style 103,British/French,dd/mm/yyyy. 19 is not a valid "numeric month", so my guess is your development server is set to british/french (19/06/2009 => Fri, 19 Jun 2009) and your production server to u.s. 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 underlying integer storage format in SQL Server. However, SQL Server might interpret a date value in a string literal format, input by an application or user for storage or to a date function, as different dates. The interpretation depends on the combination of string literal format, data type, and runtime SET DATEFORMAT, SET LANGUAGE and default language option settings.

and

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