April 20, 2012 at 5:17 am
Hi all,
I am using a tally table (Numbers) to select a sequence of dates based on a repeating event (daily, weekly, monthly etc).
It was all working fine when I did it for weeks and days but months is getting an over flow error.
I tested it and boiled it down to this test statement (you'll need a tally table somewhere to test)
DECLARE @FREQ INT
SET @FREQ = 2
SELECT DATEADD(MM, @FREQ* NUMBER, '01/05/2012') AS MYDATE, NUMBER
FROM UTILITY..NUMBERS
WHERE DATEADD(MM, @FREQ*NUMBER, '01/05/2012') < '01/05/2020'
If you set any frequency aside from 1 (ie every month) it fails on the last date to be calculated before the end date in the where clause, no matter what the end date or start dates are.
Anyone have any ideas...Im lost!
Thanks
April 20, 2012 at 5:32 am
Are you really using SQL Server 2000? Works for me on SQL Server 2008. Could be something to do with the way your dates are being interpreted. Try supplying them in the format '20120501' instead.
John
April 20, 2012 at 5:53 am
I am really using 2000, have no reason to upgrade and I don't have time to rewrite all my DTS tasks either.
DECLARE @FREQ INT
SET @FREQ = 5
SELECT DATEADD(MM, @FREQ* NUMBER, '20120501') AS MYDATE, NUMBER
FROM UTILITY..NUMBERS
WHERE DATEADD(MM, @FREQ*NUMBER, '20120501') < '20200501'
that has the same result.
Thanks
Rolf
April 20, 2012 at 6:57 am
The only thing I can think of is that there's some anomaly in your Numbers table. What does this return?
select MIN(number), MAX(number), COUNT(number)
FROM Utility.numbers where number <= 96
John
April 20, 2012 at 8:25 am
09697
(1 row(s) affected)
as you would expect I think.
Rolf
April 20, 2012 at 8:38 am
Rolf
Yes, looks OK. My Numbers table starts with 1, so you could try adding WHERE Number > 0, but to be honest I can't see how that would make a difference.
What is the exact error message that you get?
John
April 20, 2012 at 9:13 am
Its an overflow message in trying to create the date in the where clause for the last date..but it makes no sense to me because if I increase the date range it will happily go past the same date only to fail on the last one again...
Rolf
April 20, 2012 at 9:18 am
Show us the output.
April 20, 2012 at 9:42 am
John Mitchell-245523 (4/20/2012)
The only thing I can think of is that there's some anomaly in your Numbers table. What does this return?
select MIN(number), MAX(number), COUNT(number)FROM Utility.numbers where number <= 96
John
With out the limit 96, what is the max number in your numbers table?
April 20, 2012 at 9:45 am
Try this:
DECLARE @FREQ INT
SET @FREQ = 2
SELECT DATEADD(MM, @FREQ* NUMBER, '01/05/2012') AS MYDATE, NUMBER
FROM UTILITY..NUMBERS
WHERE NUMBER <= DATEDIFF(mm, '20120501', '20200501')
April 20, 2012 at 10:00 am
Thanks for the help.
Input
DECLARE @FREQ INT
SET @FREQ = 5
SELECT DATEADD(MM, @FREQ* NUMBER, '20120501') AS MYDATE, NUMBER
FROM UTILITY..NUMBERS
WHERE DATEADD(MM, @FREQ*NUMBER, '20120501') < '20200501'
Ouput
MYDATENUMBER
2012-05-01 00:00:00.0000
2012-10-01 00:00:00.0001
2013-03-01 00:00:00.0002
2013-08-01 00:00:00.0003
2014-01-01 00:00:00.0004
2014-06-01 00:00:00.0005
2014-11-01 00:00:00.0006
2015-04-01 00:00:00.0007
2015-09-01 00:00:00.0008
2016-02-01 00:00:00.0009
2016-07-01 00:00:00.00010
2016-12-01 00:00:00.00011
2017-05-01 00:00:00.00012
2017-10-01 00:00:00.00013
2018-03-01 00:00:00.00014
2018-08-01 00:00:00.00015
2019-01-01 00:00:00.00016
2019-06-01 00:00:00.00017
2019-11-01 00:00:00.00018
2020-04-01 00:00:00.00019
(20 row(s) affected)
Server: Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused overflow.
There are 65535 rows in the tally table. I dont think it is running out of numbers in the tally table and you can extend the end date in the where clause to many years in the future and the same thing happens on the last date it overflows.
Cheers
Rolf
April 20, 2012 at 10:02 am
Sorry missed your latest reply...that works..I had tried something similar before but it doesnt 'fit' in with some of the other aspects of the SP.
I'm still confused/interested as to why the original doesn't work when using the date comparison.
Rolf
April 20, 2012 at 10:07 am
I'd guess it's because it's evaluating all 65000 rows in the WHERE clause - that's about 10000 years with a freq of 2, which is beyond the range of datetime. Try adding an extra condition to your WHERE clause, limiting the Number to the highest value it's ever likely to be, and something that doesn't overflow the datetime range.
John
April 20, 2012 at 10:08 am
Ran into this myself a long time back. Took me a bit to remember. You would think your WHERE clause would stop reading from your Numbers table when it failed, but it doesn't, SQL Server is actually computing all the values using your Numbers table. When working with days and weeks, your maximum value for the number to be added didn't take the final date past 9999-12-31. When you got to months, however, now you are passing that date, and is why you are getting the error.
April 20, 2012 at 10:46 am
wow..weird....I guess it makes sense to the DB architecture and the execution plan but it doesn't make any sense to me to evaluate the DATEADD clause for all the numbers in the table beyond when the date exceeds the match.
Any DB techs out there that can explain whats going on and why...?
Thanks for the help and solution..I will go and reconfigure the SP so I can use the number of months/weeks/days and datediff.
Rolf
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply