Differences
This shows you the differences between two versions of the page.
control_error_-_datetime_outside_the_valid_range [2019/01/27 11:28] |
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/ | ||
+ | |||
+ | |||
+ | |||
+ | ===== 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. | ||
+ | |||
+ | |||
+ | |||
+ | ===Correct Query - US Format=== | ||
+ | |||
+ | |||
+ | |||
+ | This query should run successfully. | ||
+ | |||
+ | |||
+ | |||
+ | <code sql> | ||
+ | select top 15 * | ||
+ | from Journal | ||
+ | where ID >0 and ClassTypeID = 8916 | ||
+ | and QueryStartDateTime = ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===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 = ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | |||
+ | ===== Resolution ===== | ||
+ | |||
+ | |||
+ | |||
+ | Several things must be done to resolve this problem: | ||
+ | * Change all existing users to " | ||
+ | - Open the SQL Server Management Console. | ||
+ | - In SQL Object Explorer, expand " | ||
+ | - Right-click on the user to be edited | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | |||
+ | |||
+ | |||
+ | < | ||
+ | - Select " | ||
+ | - In the " | ||
+ | - Click OK to apply the changes. | ||
+ | * Change the default for newly created users to English. | ||
+ | - Open the SQL Server Management Console. | ||
+ | - In SQL Object Explorer, right-click the Server (topmost entry) | ||
+ | - Select " | ||
+ | - Select " | ||
+ | - In the " | ||
+ | * 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 " | ||
+ | - At the prompt, select " | ||
+ | |||
+ | |||
+ | |||
+ | ===== Version Information ===== | ||
+ | * Reported : 10/2010 | ||
+ | * Version(s): 3.x+ | ||
+ | |||
+ | |||
+ | |||