September 22, 2010 at 6:56 am
Hello everyone.
So I have a bit of quandary here. I have 3 sperate database servers. Each has the same physical and software setup. All or running 2005 Standard, 64 bit sql server, and 2003 windows server. SP3
Across all 3 servers last night we had a massive failure of client jobs. Each has the following error:
Conversion failed when converting datetime from character string.
Now, here are things to consider.
1) These servers have been running without issues for at least 6 months
2) there were absolutely NO changes to the code in the last 3 months
3) I came in this morning and all of the jobs that failed with this error ran without issues. Again, not a single change that I can see or find.
Does anyone have any thoughts on what might be gone on here?
Note: I have seen this once before in our test environment about 4-5 months ago where I had to change the following code:
SELECT *
FROM Orders as O
WHERE O.OrderDate BETWEEN '1/1/2010' AND '1/31/2010'
to this:
DECLARE @StartDate DATETIME
, @EndDate DATETIME
SET @StartDate = '1/1/2010'
SET @EndDAte = '1/31/2010'
SELECT *
FROM Orders as O
WHERE O.OrderDate BETWEEN @StartDate AND @EndDate
I got the error running this first query, but not the second. This was an AD-HOC query, and was not part of a store procedure as the failed jobs are.
Please note that this also happened when I did any other type of operator other than between.
Thoughts/Suggestions?
Thanks,
Fraggle
September 22, 2010 at 7:33 am
Did you notice that in your second query you are looking at a different column in the where clause?
What is the datatype of your OrderDate column? Some table definitions and some sample data would make this a lot easier. 🙂
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 22, 2010 at 7:46 am
What does SELECT @@LANGUAGE return? Date Formats are set based on the server's language setting.
Can you share the actual offending code? The table definitions would help as well.
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
September 22, 2010 at 8:09 am
SeanLange (9/22/2010)
Did you notice that in your second query you are looking at a different column in the where clause?What is the datatype of your OrderDate column? Some table definitions and some sample data would make this a lot easier. 🙂
1) Nope. Corrected my example. Thanks.
2) DATETIME for the column. Please note that this isn't the only column it would have happened on. This is just the specific column I was using when it happened a while back. The code in question is unique to each and every client on the server. 125 different databases across 3 servers. So I wouldn't be using the same column from the same table.
Fraggle
September 22, 2010 at 8:12 am
Jack Corbett (9/22/2010)
What does SELECT @@LANGUAGE return? Date Formats are set based on the server's language setting.Can you share the actual offending code? The table definitions would help as well.
Jack,
1) us_english across all servers
2) Wish I could, but I can't. With that said, this code is unique to each database that it is run against. So it failed on 125 different databases across 3 different servers. Each of those databases are using unique code.
Fraggle
September 22, 2010 at 8:22 am
The actual code would be a huge help. The dates for the where clause -- are they hard coded, or are they passed in from an external source calling the sp (like a website, or application)?
September 22, 2010 at 8:31 am
i just tried to replicate this but i am not able to see any problem.
What I did :-
1) table create as order with startdate and enddate as 2 columns(both datetime)
2) inserted the record
3) and then used the variable to return the record.
And I am able to see the records without any error.
Is column storing the values in your table is datetime?
----------
Ashish
September 22, 2010 at 8:36 am
Mike Menser (9/22/2010)
The actual code would be a huge help. The dates for the where clause -- are they hard coded, or are they passed in from an external source calling the sp (like a website, or application)?
Mike,
That is the issue. I have examples of all of these since everything is custom.
Basic setup of the proc is this. It runs on a single database in the system. It then calls to other procs and databases on the server.
as an example.
create proc database1.dbo.proctocalleverything ()
as
begin
declare @today datetime
set @today = getdate()
exec database2..customproc1
exec database3..CustomProc2 @today
if datepart(day, @today) = 5
begin
exec database4..customproc3 dateadd(day, -30, @today), @today
end
else
begin
exec database4..customproc3 '1/1/2000', @today
end
end
I can keep going with the examples. But I think you probably have a picuture of it now.
Fraggle.
September 22, 2010 at 8:38 am
learningforearning (9/22/2010)
i just tried to replicate this but i am not able to see any problem.What I did :-
1) table create as order with startdate and enddate as 2 columns(both datetime)
2) inserted the record
3) and then used the variable to return the record.
And I am able to see the records without any error.
Is column storing the values in your table is datetime?
Learning,
That is part of the issue. I can't replicate it. The code from my original example was the code I happened to be running when I got it. I have never been able to replicate it. However, apparently the server has been able to replicate it on a much grander scale.
Fraggle
September 22, 2010 at 9:50 am
Figured out the issue. Someone hacked into the server, change the default language, did something, and then changed it back to the default. Now I have other fun things to look into.
Thanks for all of the help.
Fraggle
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply