Error Message
This WIKI page address the following error:
“The conversion of char-value to a datetime has resulted in a datetime outside the range”
Explanation of Error
All Control internal SQLs use the US format for dates (dd/mm/yy). This error can occur when the date formatting of the SQL server does not match the date formatting being used by Control.
Severity
High. Control is not usable if the day of the month is > 12. Even when less, the results are scrambled dates!
Test Query
The following queries can be used to test for this problem. The first query should work if the system is configured for US dates. The second query uses and international date format. It should fail on a working system, but will run on a mis-configured system.
Correct Query - US Format
This query should run successfully. If it does, the date format is set correctly in SQL.
SELECT top 15 * FROM Journal WHERE ID >0 AND ClassTypeID = 8916 AND QueryStartDateTime = '03/28/2010 2:49 PM'
InCorrect Query - International Format
This query should fail. If it does not, the date format is not set correctly in SQL.
SELECT top 15 * FROM Journal WHERE ID >0 AND ClassTypeID = 8916 AND QueryStartDateTime = '28/03/2010 2:49 PM'
Resolution
Several things must be done to resolve this problem:
- Change all existing users to “English”
- Open the SQL Server Management Console.
- In SQL Object Explorer, expand “Security” and click on “Logins”.
- Right-click on the user to be edited
- Select “Properties”
- In the “Default Language” drop-down menu, select “English”
- Click OK to apply the changes.
- Change the default for newly created users to English. This will ensure that any new users are created with the correct language settings.
- Open the SQL Server Management Console.
- In SQL Object Explorer, right-click the Server (topmost entry)
- Select “Properties”
- Select “Advanced” from the list on the left
- In the “Default Language” drop-down menu, select “English”
- Restart SQL Server so the changes you made above will take affect.
- Make sure the SSLIP (and therefore all copies of Control) are closed.
- Close any open query windows in SQL.
- In SQL Object Explorer, right-click the Server (topmost entry)
- Select “Restart”
- At the prompt, select “Yes”
Version Information
- Reported : 10/2010
- Version(s): 3.x+