DATEADD FUNCTION WITH TALLY TABLE GIVE OVERFLOW ERROR WITH MONTH

Question

DATEADD FUNCTION WITH TALLY TABLE GIVE OVERFLOW ERROR WITH MONTH

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Answer, with out restricting the number of rows from the Tally table, SQL executes the dateadd function for every row in Tally until it errors.

😎

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 2

that would be if i didn't have a where clause right...

this was my where clause:

DATEADD(m,t.N-1, @DateStart) <= @DateEnd

It gave me the correct rows but errored as well???

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

There is no restriction there on the number of rows being returned from Tally. That is why you should add a second condition to the where clause, such as: and t1.N <= some value.

SQL doesn't know how many rows it needs to meet the existing criteria.

😎

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 4

It sounds like your saying that my where clause was followed and SQL stopped making rows, but continued on with the query any way???

below instead of going for months, i went for days and no error... this where cluase work here but not for month... are you seeing why i am confused...

I like your persistance... TY

DECLARE @DateStartDATETIME

DECLARE @DateEndDATETIME

DECLARE @MONTHSINT

SET @DateStart = '2008-08-25' -- 00:00:00.000

SET @DateEnd = '2013-08-25'

SELECT

DATEADD(D,t.N-1, @DateStart) AS NEXT_PAY_DATE

FROM dbo.Tally t

WHERE DATEADD(D,t.N-1, @DateStart) <= @DateEnd

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Remember when I said there were 97199 months between 1900-01-01 and 9999-12-31? Well, if you multiply 97,199 by 365 you get 35,477,635. Your Tally table (and mine) has 1,000,000 rows, so we never get close to the date 9999-12-31, so we don't get the error when you use dd in the dateadd function.

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

FYI...

select dateadd(dd, 1000000, '2008-10-22')

returns: 4746-09-19

😎

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 7

Remember how queries work:

First, process the joins. In this case there are no joins - so we need to work on the full set of data which is 1,000,000 rows.

Next, process the where clause. As soon as the system tries to add a number to your start date that will generate a date greater than 9999-12-31, you generate the error.

You don't see this error because there are more than a million days between your start date and 9999-12-31 so the query can process all rows and eliminate any rows with a date greater than your end date.

By using months (which has a lot less than one million between your start date and 9999-12-31), the system hits the overflow error before it can eliminate the results.

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

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 8

I think i get...

I'm thinking my where clause (for months) was comparing

WHERE DATEADD(m,t.N-1, @DateStart) <= @DateEnd

would be equivilant to below

WHERE '2013-08-25 00:00:00.000' <= '2013-08-25 00:00:00.000'

thus when true it would stop... but your saying that it's comparing that date multiply stuff you did early...lol

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 9

Not really - it is actually simpler than that. The where clause has to be evaluated for every row in the set. Since your table has 1,000,000 rows - it has to be evaluated for all 1,000,000 numbers.

Therefore, the calculation exceeds the allowable dates and causes an overflow error.

By adding a check for N 100000 you will still get the same error.

Example:

WHERE n>=100000

AND n < 130000

AND dateadd(m, n-1, @DateStart) <= @DateEnd

You are processing only 30,000 rows - but the value of N is going to cause an overflow.

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

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 10

I didn't know that...

so if i made a tally table with less rows (below that of the highest date SQL server has) I could avoid the error that way as well...

You guys are making me so smart.. 😀

Thanks...

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 11

Well, sure - that is why I originally asked how many rows you had in your Tally table. However, even if you limit your Tally table to 11,000 rows (recommended for most processes) - you can cause the same problem.

Try the following: select dateadd(year, 11000, '2008-10-25');

You'll get the same exact error, for the same exact reason.

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Which comes back to limiting the number of rows queried from Tally. For example:

and t1.N <= datediff(mm, @startdate, @enddate) -- you might want to add a + 1 here also.

-- change the mm to what ever datepart you are using in your query.

😎

Sergiy SSC Guru Points: 110208 More actions · Answer 13

john.steinbeck (10/22/2008)
It sounds like your saying that my where clause was followed and SQL stopped making rows, but continued on with the query any way???

Your WHERE clause does not limit number of rows from Tally table in any way.

It limits value received as a result from calculation based on value N, and because SQL Server cannot predict results of that calculation it must do it for EVERY row in the table.

To limit number of rows you need to apply WHERE limitations to one of the values in the rows.

Just like Lynn did in his last post.

And remember the rule - never use functions on table columns in WHERE clause.

_____________
Code for TallyGenerator

john.steinbeck Ten Centuries Points: 1015 More actions · Answer 14

I get it now, thank you all so much...

"And remember the rule - never use functions on table columns in WHERE clause. "

Is this like the CROSS APPLY???

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 15

Bottome line is, don't do calculations on t.N in the WHERE clause or all of the Tally table must be evaluated... as I saw previously suggested, this works the best for speed as well as preventing the error....

DECLARE @DateStart DATETIME

DECLARE @DateEnd DATETIME

SET @DateStart = '2008-08-25' -- 00:00:00.000

SET @DateEnd = '2013-08-25'

SELECT DATEADD(mm,t.N-1, @DateStart) AS NEXT_PAY_DATE

FROM dbo.Tally t

WHERE t.n <= DATEDIFF(mm,@DateStart,@DateEnd)-1

And, no... nothing really like a Cross Apply because there's no correlation. Just a simple "pseudo cursor".

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)