October 22, 2008 at 2:19 pm
Hi,
Can anyone help me understand why I am getting the overflow error when using Month... it works fine with day???
Thanks,
John
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SET @DateStart = '2008-08-25 00:00:00.000'
SET @DateEnd = '2013-08-25 00:00:00.000'
SELECT
DATEADD(M,t.N - 1, @DateStart) AS NEXT_PAY_DATE
FROM dbo.Tally t
WHERE DATEADD(M,t.N - 1, @DateStart) <= @DateEnd
Adding a value to a 'datetime' column caused overflow
October 22, 2008 at 2:35 pm
That code executes properly on my box. It also executes without the where for all 30,000 lines of my current Tally Table. (Up to year 4508)
October 22, 2008 at 2:39 pm
Is that exact code failing on your server, or is that a representation of what you're trying to do?
October 22, 2008 at 2:41 pm
Hi,
Thanks for replying... can you tell I'm sitting here waiting...lol
I am on SQL 2000, is that a difference? With the where clause it's totally ignoring it and mine goes until the my tally table runs out of numbers...
I just changed the where clause to where t.n <=60 and no issues...
but i don't want that, cause then thats just another calculation....
John
here is a different one by Jim... no problems
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
-- 2008-08-31 00:00:00.000
SET @DateStart = '2008-08-25 00:00:00.000'
SET @DateEnd = '2013-08-25 00:00:00.000'
SELECT (t.N-1+30)+@DateStart AS ShippedDate
FROM dbo.Tally t
WHERE (t.N-1+30)+@DateStart <= @DateEnd
October 22, 2008 at 2:43 pm
that is my exact code... running in a query... but it is a function where the begin and end dates will be passed in...
October 22, 2008 at 2:45 pm
How many rows do you have in your Tally table? I run this fine with 11000 rows.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
October 22, 2008 at 2:48 pm
1 MILLION which is below the size of an integer correct
October 22, 2008 at 2:57 pm
john.steinbeck (10/22/2008)
that is my exact code... running in a query... but it is a function where the begin and end dates will be passed in...
Aha. That's the piece of information I was looking for. Check the dates being passed in, one of them will be out of range, or in a bad format.
Try adding this to your function:
SET @DateStart = CONVERT(datetime,@DateStart,101)
SET @DateEnd = CONVERT(datetime,@DateEnd,101)
It may still fail if one is in a bad format, but it may give you a different error, and that would confirm that a bad date is being passed.
October 22, 2008 at 3:01 pm
But this code I posted first thing, if i paste this into my query on SQL Sever Management Studio I get the error... is this the code you copied and ran yourself? Cause this is whats doing it to me...
DECLARE @DateStart DATETIME
DECLARE @DateEnd DATETIME
SET @DateStart = '2008-08-25 00:00:00.000'
SET @DateEnd = '2013-08-25 00:00:00.000'
SELECT
DATEADD(M,t.N - 1, @DateStart) AS NEXT_PAY_DATE
FROM dbo.Tally t
WHERE DATEADD(M,t.N - 1, @DateStart) <= @DateEnd
October 22, 2008 at 3:07 pm
Yep, I pasted it directly into QA on my SQL 2000 server and ran it. I get 61 rows returned.
October 22, 2008 at 3:10 pm
The number of months between 1900-01-01 and 9999-12-31 is 97199. Your query, even though it stops at '2013-08-25 00:00:00.000' still attempts to calculate dateadd through the entire Tally table. If you add and t1.N < 30000 -- an arbitrary number
it works just fine.
😎
October 22, 2008 at 3:12 pm
I want your machine, dangit...
I ran it again, it does produce the 61 rows but I also get the error thrown up... here is a pic
October 22, 2008 at 3:20 pm
hi Lynn,
The number of months between 1900-01-01 and 9999-12-31 is 97199...
My query does produce the 61 rows (or months) and it still throws up the error... so am i doing 1 million rows or just the 61... was the loop to fast...lol
yes changing the where clause doesn't cause the error, but 1 that is more work, 2nd I can't take no for an answer, 3rd I didn't produce over 97199 rows so why the error?????????????
John
October 22, 2008 at 3:21 pm
john.steinbeck (10/22/2008)
I want your machine, dangit...I ran it again, it does produce the 61 rows but I also get the error thrown up... here is a pic
I get the same error you do if I don't restrict the number of rows accessed from my Tally table. Add the additional restriction on your query to keep N (the number from the tally table) to a small and resonable number, say datediff(mm, @startdate, @enddate) + 1.
I think you'll see that you don't get the error message any more.
😎
October 22, 2008 at 3:26 pm
DOH, you're so smart... thanks...
But I still want to know why!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! :w00t:
LOL
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply