Wednesday 31 October 2012

sqlserver - conversion of a varchar data type to a datetime data type resulted in an out-of-range value

I have an app connecting to a microsoft sqlserver DB.

When I ran the app all was fine & when the tester ran the app it failed with the following Exception:


Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet$FetchBuffer.nextRow(SQLServerResultSet.java:4853)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.fetchBufferNext(SQLServerResultSet.java:1781)
at com.microsoft.sqlserver.jdbc.SQLServerResultSet.next(SQLServerResultSet.java:1034)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.apache.commons.dbcp.DelegatingResultSet.next(DelegatingResultSet.java:207)
at org.hibernate.loader.Loader.doQuery(Loader.java:825)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
at org.hibernate.loader.Loader.doList(Loader.java:2542)
... 41 more


It took me ages to work out what the issue was until I noticed that I was connecting to the database as 'sa' & he was using the schema name.

There is a lot of info on the web about this issue concerning changing the systems Regional settings but that was not my issue. In this case the sqlserver user 'sa' had a Default language of 'English' and the schema user had a language of 'British English'. Changing the Default Language to 'English' solved the problem.