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”
  1. Open the SQL Server Management Console.
    1. In SQL Object Explorer, expand “Security” and click on “Logins”.
    2. Right-click on the user to be edited

> At a minimum, do the "cyrious" and "Administrator" users.

> It is recommended to do this on all users unless SQL is being used by a different program.

  1. Select “Properties”
  2. In the “Default Language” drop-down menu, select “English”
  3. 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.
  1. Open the SQL Server Management Console.
  2. In SQL Object Explorer, right-click the Server (topmost entry)
  3. Select “Properties”
  4. Select “Advanced” from the list on the left
  5. In the “Default Language” drop-down menu, select “English”
  • Restart SQL Server so the changes you made above will take affect.
  1. Make sure the SSLIP (and therefore all copies of Control) are closed.
  2. Close any open query windows in SQL.
  3. In SQL Object Explorer, right-click the Server (topmost entry)
  4. Select “Restart”
  5. At the prompt, select “Yes”

Version Information

  • Reported : 10/2010
  • Version(s): 3.x+
You could leave a comment if you were logged in.