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 @DateStartDATETIME
DECLARE @DateEndDATETIME
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 @DateStartDATETIME
DECLARE @DateEndDATETIME
--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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
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
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