May 21, 2010 at 8:03 am
Hi at all.
I am working in a company which sells a product to a lot of customer worldwide.
This product runs some SQL Queries for example which is transferring data from one table into another. As well there is a Query which is purging old data from database via this simple SQL Query:
DELETE FROM <table> WHERE time < <datetime>;
On for example 90% of the sites this job is running perfectly fine but we have about 10% which are running into this error:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
The statement has been terminated
I checked the Regional Settings as well changed the language and dateformat for the user:
SET LANGUAGE deutsch
SET DATEFORMAT DMY
Unfortunately nothing helped with changing these. The only thing which was working was to open the properties of the user and set the "Default Language" in the "General Tab" to German.
Afterwards this was working but soon after the other SQL Queries failed with the same error.
I've already checked and compared the sites which are running fine and the one which are not.
No success so far in seeing any difference.
Is there something else I can check on and so on?
P.S. I cannot change the queries so it has to be something else.
Thanks a lot in advance,
David
May 21, 2010 at 10:34 am
Is the time column a proper datetime/smalldatetime column? Are you passing the datetime as a datetime/smalldatetime parameter or as a string?
One way to fix avoid these types of problems is to use the yyyymmdd hh:mm:ss format whenever passing dates and times. See BOL on DateTime, http://msdn.microsoft.com/en-us/library/ms187819.aspx.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 21, 2010 at 11:13 am
Hi Jack.
Thanks for your reply.
Yes the column is a proper datetime data type and I am passing the following parameter for the datefield in the where clause:
DELETE FROM <table> WHERE date = '4/21/2008 12:00:00AM';
Like mentioned before, I have no way of changing anything in regards to the query, and since it is working on 90% of the sites I am assuming this is a configuration issue either in MSSQL or the environment.
May 26, 2010 at 7:30 am
Does noone has a solution to my problem? 🙁
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply