January 19, 2012 at 5:09 am
All,
Im doing enhancement for VB.NET 2005 code to include holiday request for the user. Insertion in live system for the following code works but test system fails to insert data due to the following error
"The conversion of a varchar data type to a datetime datetype resulted in an out-of-range value. The statement has been terminated.
Query:
INSERT INTO OutOfOfficeData ( [PersonID], [StartDate], [EndDate], [Description], [OutType] )
VALUES (244, '2012-01-26 00:00:00', '2012-01-27 00:00:00', 'Annual leave', 'AL' )
Live system:
Product Version Level Product edition
9.00.3042.00SP2Express Edition with Advanced Services
Test System
10.50.1600.1RTMExpress Edition with Advanced Services
System Column Name Column type
Live System StartDate DateTime
Live System EndDate DateTime
Test System StartDate DateTime
Test System EndDate DateTime
Please can anyone point me in the right direction?
Ta,
Vids
January 19, 2012 at 5:56 am
You might have a US/UK date issue.
Try this to prove it...
INSERT INTO OutOfOfficeData ( [PersonID], [StartDate], [EndDate], [Description], [OutType] )
VALUES (244, '26 Jan 2012 00:00:00', '27 Jan 2012 00:00:00', 'Annual leave', 'AL' )
The above should force it to convert to what it wants to use.
January 19, 2012 at 7:24 am
Hey it worked.
Thanks,
Vids
January 19, 2012 at 8:22 am
Hey,
Can this also be affected by sever collation? Live server is SQL1_Latin but my test is Latin1_General.
Thanks,
Vids
January 19, 2012 at 9:11 am
It is most likely due to differences in dateformat. run DBCC USEROPTIONS on both servers and the dateformat is likely different.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply