DATEADD FUNCTION WITH TALLY TABLE GIVE OVERFLOW ERROR WITH MONTH

  • 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

  • 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)

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • Is that exact code failing on your server, or is that a representation of what you're trying to do?

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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

  • that is my exact code... running in a query... but it is a function where the begin and end dates will be passed in...

  • 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

  • 1 MILLION which is below the size of an integer correct

  • 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.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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

  • Yep, I pasted it directly into QA on my SQL 2000 server and ran it. I get 61 rows returned.

    Seth Phelabaum


    Consistency is only a virtue if you're not a screwup. 😉

    Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

  • 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.

    😎

  • 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

  • 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

  • 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.

    😎

  • 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