Differences

This shows you the differences between two versions of the page.

Link to this comparison view

control_error_-_datetime_outside_the_valid_range [2019/01/27 11:28] (current)
Line 1: Line 1:
 +====== ​ ======
 +
 +
 +
 +===== 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.
 +
 +
 +
 +<code sql>
 +select top 15 *
 +from Journal
 +where ID >0 and ClassTypeID = 8916 
 +and QueryStartDateTime = '​03/​28/​2010 2:49 PM'
 +</​code>​
 +
 +
 +
 +===InCorrect Query - International Format===
 +
 +
 +
 +This query should fail.  If it does not, the date format is not set correctly in SQL.
 +
 +
 +
 +<code sql>
 +select top 15 *
 +from Journal
 +where ID >0 and ClassTypeID = 8916 
 +and QueryStartDateTime = '​28/​03/​2010 2:49 PM'
 +</​code>​
 +
 +
 +
 +===== 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 ​
 +
 +
 +
 +<​html><​div style="​margin-left:​ 40px;">>​ At a minimum, do the "​cyrious"​ and "​Administrator"​ users. </​div></​html>​
 +
 +
 +
 +<​html><​div style="​margin-left:​ 40px;">>​ It is recommended to do this on all users unless SQL is being used by a different program. </​div></​html>​
 +    - 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+
 +
 +
 +