Error encountered while executing TSQL insert statement

Question

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?

Marc Gravell · Accepted Answer · 2009-06-19 22:58:51Z

3

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?

answered Jun 19, 2009 at 22:58

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mehrdad Afshari · Accepted Answer · 2009-06-19 22:59:22Z

2

The problem is with ``19/06/2009'which is specified inDD/MM/YYYYbut your SQL Server instance expects it asMM/DD/YYYY`.

answered Jun 19, 2009 at 22:59

Mehrdad Afshari

424k93 gold badges864 silver badges796 bronze badges

Comments

George Mastros · Accepted Answer · 2009-06-19 23:03:24Z

2

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

answered Jun 19, 2009 at 23:03

George Mastros

24.6k5 gold badges56 silver badges64 bronze badges

Comments

VolkerK · Accepted Answer · 2009-06-19 23:25:01Z

0

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.

edited Jun 19, 2009 at 23:25

answered Jun 19, 2009 at 23:17

VolkerK

96.3k20 gold badges169 silver badges232 bronze badges

1 Comment

titanium Over a year ago

We changed the logins default language from English to British English and running the insert statement no longer throws an error. Your answer/guess pretty much answer the question. Your answer/guess was further reinforced by your quote from MSDN which is a good points to follow. Thanks.

Collectives™ on Stack Overflow

Error encountered while executing TSQL insert statement

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related