January 10, 2012 at 11:25 am
I have a database restored on two seperate servers. The data in the database on each server is identical.
On serverA, when i do select * from viewA, i get all rows returned.
On severB when i run the same query i get the following error:
Conversion failed when converting date and/or time from character string.
The view converts some columns from a varchar to a smalldatetime. View is identical on both servers also.
Ive checked for database configuration settings and they are identical on both servers.
ive check collation and its identical on both servers.
Language selected for each instance is identical.
Any idea what i should check next?
January 10, 2012 at 11:38 am
winston Smith (1/10/2012)
I have a database restored on two seperate servers. The data in the database on each server is identical.On serverA, when i do select * from viewA, i get all rows returned.
On severB when i run the same query i get the following error:
Conversion failed when converting date and/or time from character string.
The view converts some columns from a varchar to a smalldatetime. View is identical on both servers also.
Ive checked for database configuration settings and they are identical on both servers.
ive check collation and its identical on both servers.
Language selected for each instance is identical.
Any idea what i should check next?
If it were me, I'd find which column is failing the conversion, then once I find which column was failing, I'd find which row was failing for that column. Then once I found the column and row, I'd have a look at the server it is supposedly not failing on. Since it isn't failing on the other server, theres no real sense in searching that one first, right?
January 10, 2012 at 11:53 am
I have narrowed it down to the 355th row in the table. But this issue doesnt indicate a data problem, it indicates a database/instance/server configuration difference.
The exact same data, selected by the exact same code works on one server but not on the other. as mentioned its a varchar col being converted to a small date time.
Is there a server setting where the date format is set? i.e. if serverB is using american format dates where as serverA is using european format dates, that could be the problem.
I have not found at an instance level where to check the default date format, or if the instance takes this from the actual server its installed on.
January 10, 2012 at 12:10 pm
winston Smith (1/10/2012)
I have narrowed it down to the 355th row in the table. But this issue doesnt indicate a data problem, it indicates a database/instance/server configuration difference.
It's often DATEFORMAT (DMY versus MDY) where the string data is in an ambiguous format. It sort of is a data problem: dates should be stored as properly typed dates. If you do have to use a string format (why?) use an unambiguous one like ISO 8601. Also, use CONVERT with an explicit style parameter instead of CAST to avoid the DMY/MDY issue. There is a huge amount of documentation on dates and times in the Books Online help system, for example:
http://msdn.microsoft.com/en-us/library/ms180878.aspx
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 10, 2012 at 12:12 pm
Run DBCC USEROPTIONS on both servers. That will show you the dateformat.
_______________________________________________________________
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/
January 10, 2012 at 12:15 pm
SQL Kiwi (1/10/2012)
If you do have to use a string format (why?)
Its not a system that i had any input in the design for. Its in existance many years before I got to work on it. Im writing packages for a system thats already in existance and which i cannot make changes to.
Il have a look at the useroptions tomorrow when im back in work to confirm if thats the issue. il update once i have more info. thanks guys!
January 10, 2012 at 12:21 pm
Check the topic "DATETIME Query problem" on the 1st page of this forum.
Might be exactly the same issue.
_____________
Code for TallyGenerator
January 10, 2012 at 12:24 pm
winston Smith (1/10/2012)
Its not a system that i had any input in the design for. Its in existance many years before I got to work on it. Im writing packages for a system thats already in existance and which i cannot make changes to.
You can at least use CONVERT instead of CAST in your code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 2:51 am
DBCC UserOptions showed the date format on the working server was DMY but on the non working server was MDY.
I changed this with the SET DATEFORMAT DMY option.
Issue was still occurring though, and ended up having to change the default language of the logins to British English instead of just English.
Issue is now resolved.
I had selected the correct British English Language when instaling sql server, so im wondering, what could i have done differently during installation to ensure this wouldnt happen?
thanks for the help all.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply