November 3, 2015 at 9:52 pm
I have a table "Numbers" with a single int field "N". The records go from 0 through 100,000.
I'm trying to generate a sequential list of months starting with Jan-2013 and stopping at Oct-2015 using that table and the DateAdd function.
This causes the error "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow.":
SELECT
n, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
dateadd(month,n,'20130101') <= '20151001'
If I add a limit of 40 to the "N" value the result set stops at N = 33 and the month of 10/1/2015:
SELECT
n, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
dateadd(month,n,'20130101') <= '20151001'
and n <= 40
This also works:
SELECT
n, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
N <= datediff(month,'20130101','20151001')
I can't figure out why the WHERE clause in the first query doesn't stop it when it reaches Oct-2015. I use almost the exact same statement to return a range of days with no problem:
SELECT
dateadd(day,N,@LastDay) as Dte
INTO
#tmp
FROM
dbo.numbers
WHERE
dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0)
Any idea why the first query above doesn't stop when dateadd(month,N,'20130101') passes Oct-2015?
TIA
November 4, 2015 at 12:20 am
Sorry I can't help.
Whatever the problem is, it's fixed now. Cannot reproduce in 2014.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2015 at 10:28 am
The only thing I can think of that would cause this is if you have an unordered table, where n in one of the first rows is too big for DateAdd to handle.
You write that the numbers table contain numbers up to 100000. If you try to add 100000 months to your date, you WILL get an overflow error.
Your other examples filters directly on the N value, so such a high number would never be added.
Try to ensure that your numbers table is ordered and see if that doesn't help.
November 5, 2015 at 11:13 am
kaj (11/5/2015)
The only thing I can think of that would cause this is if you have an unordered table, where n in one of the first rows is too big for DateAdd to handle.You write that the numbers table contain numbers up to 100000. If you try to add 100000 months to your date, you WILL get an overflow error.
Your other examples filters directly on the N value, so such a high number would never be added.
Try to ensure that your numbers table is ordered and see if that doesn't help.
You might just be on to something here. My numbers table went to 11,000 (Spinal Tap would be proud!)
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 5, 2015 at 2:43 pm
In your first query, the engine first has to evaluate the DATEADD before it can evaluate the condition. Since you have numbers that cause an overflow when evaluating the DATEADD, the query raises an error.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 5, 2015 at 8:13 pm
Thanks to all for the replies
kaj (11/5/2015)
The only thing I can think of that would cause this is if you have an unordered table, where n in one of the first rows is too big for DateAdd to handle.You write that the numbers table contain numbers up to 100000. If you try to add 100000 months to your date, you WILL get an overflow error.
Your other examples filters directly on the N value, so such a high number would never be added.
Try to ensure that your numbers table is ordered and see if that doesn't help.
The table has a clustered primary key on the only field ("N"). Would that eliminate the problem you mention here?
drew.allen (11/5/2015)
In your first query, the engine first has to evaluate the DATEADD before it can evaluate the condition. Since you have numbers that cause an overflow when evaluating the DATEADD, the query raises an error.Drew
Does that mean the engine performs the calculation in the SELECT clause for the entire numbers table before applying the WHERE condition? I thought it would apply the WHERE condition to each row as it's selected and stop as soon as it evaluates to true. I still don't understand how it's different from this one that works:
SELECT
dateadd(day,N,@LastDay) as Dte
INTO
#tmp
FROM
dbo.numbers
WHERE
dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0)
November 6, 2015 at 12:14 am
ArcticEd32 (11/5/2015)
Thanks to all for the replieskaj (11/5/2015)
The only thing I can think of that would cause this is if you have an unordered table, where n in one of the first rows is too big for DateAdd to handle.You write that the numbers table contain numbers up to 100000. If you try to add 100000 months to your date, you WILL get an overflow error.
Your other examples filters directly on the N value, so such a high number would never be added.
Try to ensure that your numbers table is ordered and see if that doesn't help.
The table has a clustered primary key on the only field ("N"). Would that eliminate the problem you mention here?
drew.allen (11/5/2015)
In your first query, the engine first has to evaluate the DATEADD before it can evaluate the condition. Since you have numbers that cause an overflow when evaluating the DATEADD, the query raises an error.Drew
Does that mean the engine performs the calculation in the SELECT clause for the entire numbers table before applying the WHERE condition? I thought it would apply the WHERE condition to each row as it's selected and stop as soon as it evaluates to true. I still don't understand how it's different from this one that works:
SELECTdateadd(day,N,@LastDay) as Dte
INTO
#tmp
FROM
dbo.numbers
WHERE
dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0)
Well, I think it's been explained quite well.
The first one fails and the second does not:
select dateadd(month, 100000, '20130101') as RptMonth;
select dateadd(day, 100000, '20130101') as RptMonth;
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 6, 2015 at 2:52 am
ArcticEd32 (11/5/2015)
Thanks to all for the replieskaj (11/5/2015)
The only thing I can think of that would cause this is if you have an unordered table, where n in one of the first rows is too big for DateAdd to handle.You write that the numbers table contain numbers up to 100000. If you try to add 100000 months to your date, you WILL get an overflow error.
Your other examples filters directly on the N value, so such a high number would never be added.
Try to ensure that your numbers table is ordered and see if that doesn't help.
The table has a clustered primary key on the only field ("N"). Would that eliminate the problem you mention here?
drew.allen (11/5/2015)
In your first query, the engine first has to evaluate the DATEADD before it can evaluate the condition. Since you have numbers that cause an overflow when evaluating the DATEADD, the query raises an error.Drew
Does that mean the engine performs the calculation in the SELECT clause for the entire numbers table before applying the WHERE condition? I thought it would apply the WHERE condition to each row as it's selected and stop as soon as it evaluates to true. I still don't understand how it's different from this one that works:
SELECTdateadd(day,N,@LastDay) as Dte
INTO
#tmp
FROM
dbo.numbers
WHERE
dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0)
Putting the row restriction in the WHERE clause means the entire numbers table will be read and you also run the risk of the calculations being performed in an order that you don't expect, causing the overflow error. Use TOP instead. Compare the execution plans of these two queries:
DECLARE @LastDay DATETIME = GETDATE() - 30
DROP TABLE #tmp1;DROP TABLE #tmp2;
;WITH
N1 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
N2 AS (SELECT n = 0 FROM N1 a, N1 b),
N4 AS (SELECT n = 0 FROM N2 a, N2 b),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM N4)
SELECT dateadd(day,N,@LastDay) as Dte
INTO #tmp1
FROM iTally
WHERE dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0); -- cast yesterday as DATE
-- 28 rows
;WITH
N1 AS (SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) d (n)),
N2 AS (SELECT n = 0 FROM N1 a, N1 b),
N4 AS (SELECT n = 0 FROM N2 a, N2 b),
iTally AS (SELECT n = ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM N4)
SELECT TOP(DATEDIFF(DAY,@LastDay,GETDATE())-2)
dateadd(day,N,@LastDay) as Dte
INTO #tmp2
FROM iTally;
-- 28 rows
SELECT * FROM #tmp1
SELECT * FROM #tmp2
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 7, 2015 at 8:09 am
Understood now - adding 100,000 days OK; adding 100,000 months overflow
Thank you for the education!
November 7, 2015 at 11:50 am
ArcticEd32 (11/3/2015)
I have a table "Numbers" with a single int field "N". The records go from 0 through 100,000.I'm trying to generate a sequential list of months starting with Jan-2013 and stopping at Oct-2015 using that table and the DateAdd function.
This causes the error "Msg 517, Level 16, State 1, Line 1 Adding a value to a 'datetime' column caused overflow.":
SELECT
n, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
dateadd(month,n,'20130101') <= '20151001'
If I add a limit of 40 to the "N" value the result set stops at N = 33 and the month of 10/1/2015:
SELECT
n, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
dateadd(month,n,'20130101') <= '20151001'
and n <= 40
This also works:
SELECTn, dateadd(month,n,'20130101') as RptMonth
FROM
dbo.numbers
WHERE
N <= datediff(month,'20130101','20151001')
I can't figure out why the WHERE clause in the first query doesn't stop it when it reaches Oct-2015. I use almost the exact same statement to return a range of days with no problem:
SELECT
dateadd(day,N,@LastDay) as Dte
INTO
#tmp
FROM
dbo.numbers
WHERE
dateadd(day,N,@LastDay) <= dateadd(day,datediff(day,0,getdate())-1,0)
Any idea why the first query above doesn't stop when dateadd(month,N,'20130101') passes Oct-2015?
TIA
It's been alluded to in the other posts but the reason why it doesn't stop is because it's a non-SARGable query and you're also thinking in terms of rows (RBAR) instead of columns.
The problem with your original query is that it must evaluate ALL values of "N" in dateadd(month,n,'20130101') <= '20151001' because it's not evaluating by row... it's evaluating by column. This problem is the usual problem when people say that they have performance problems with a Tally or Numbers table.
Your third query above is the correct way to do this problem.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 9, 2015 at 2:29 am
It's been alluded to in the other posts but the reason why it doesn't stop is because it's a non-SARGable query and you're also thinking in terms of rows (RBAR) instead of columns.
The problem with your original query is that it must evaluate ALL values of "N" in dateadd(month,n,'20130101') <= '20151001' because it's not evaluating by row... it's evaluating by column. This problem is the usual problem when people say that they have performance problems with a Tally or Numbers table.
Your third query above is the correct way to do this problem.
It seems to me that the Query Optimizer should have taken care of that problem, i.e. automatically converted the query from the one the OP originally used to the one you promote. After all the program knows everything about how it works behind the scenes, much better than we mere humans do. People might know these things on a theoretical level (or not), but we're not machines and do not (always) have the analytical capacity of one.
Does later versions of SQL Server, for example MS SQL Server 2014, do a better job of helping in this or similar situations?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply