Adding a value to a 'datetime' column caused overflow in where part of query (ok in select)

Getting this error

'Adding a value to a 'datetime' column caused overflow.'

when Im trying to add 1 or 15 years to a datetime field.  When I call the code in the select part of the query it works, however in the where part it returns the error.

If I hardcode the start date ( tbPurchaseItem.dtStart) to '2007-04-10 00:00:00.000' which is the first records value it works.

Any ideas please?


select top 1 tbCustomer.iCustomerId,
       tbPurchaseItem.iPurchaseItemId,
       tbPurchaseItem.dtStart as dtStart,
 
       convert(datetime,case tbProduct.cDuration
                                 when 'D' then dateadd(day,  (iDuration ),tbPurchaseItem.dtStart)
                                 when 'W' then dateadd(week, (iDuration ),tbPurchaseItem.dtStart)
                                 when 'M' then dateadd(month,(iDuration ),tbPurchaseItem.dtStart)
                                 when 'Y' then dateadd(year, (iDuration ),tbPurchaseItem.dtStart)
                            end)
       as dtFinish
       from tbCustomer
       inner join tbPurchase
               on tbPurchase.iCustomerId = tbCustomer.iCustomerId
       inner join tbPurchaseItem
               on tbPurchaseItem.iPurchaseId = tbPurchase.iPurchaseId
              and tbPurchaseItem.dtCancel is null
       inner join tbProduct
               on tbProduct.iProductId = tbPurchaseItem.iProductId
              and tbProduct.cDuration <> 'N'
       where (tbProduct.iProductGroupId = 437)
	 
         and (convert(datetime,tbProduct.cDuration
when 'D' then dateadd(day,  (iDuration ),tbPurchaseItem.dtStart)
                                 when 'W' then dateadd(week, (iDuration ),tbPurchaseItem.dtStart)
                                 when 'M' then dateadd(month,(iDuration ),tbPurchaseItem.dtStart)
                                 when 'Y' then dateadd(year, (iDuration ),tbPurchaseItem.dtStart)
ELSE 0
                            end) >= '24 mar 2009')
--tbProduct.cDuration = 'Y'
--iDuration = 15

Open in new window

louise_8Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Hi,

Could you please try (hopefully without change) the following ?


select top 1 tbCustomer.iCustomerId,
       tbPurchaseItem.iPurchaseItemId,
       tbPurchaseItem.dtStart as dtStart,
       case tbProduct.cDuration
            when 'D' then dateadd(day,  (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'W' then dateadd(week, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'M' then dateadd(month,(iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'Y' then dateadd(year, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
       else tbPurchaseItem.dtStart
       end as dtFinish
 
       from tbCustomer
       inner join tbPurchase
               on tbPurchase.iCustomerId = tbCustomer.iCustomerId
       inner join tbPurchaseItem
               on tbPurchaseItem.iPurchaseId = tbPurchase.iPurchaseId
              and tbPurchaseItem.dtCancel is null
       inner join tbProduct
               on tbProduct.iProductId = tbPurchaseItem.iProductId
              and tbProduct.cDuration <> 'N'
       where (tbProduct.iProductGroupId = 437)
	 
       and   (isnull(tbPurchaseItem.dtStart,'19000101') >= 
                case tbProduct.cDuration
                when 'D' then dateadd(day,  -1*(iDuration ),'24 mar 2009')
                when 'W' then dateadd(week, -1*(iDuration ),'24 mar 2009')
                when 'M' then dateadd(month,-1*(iDuration ),'24 mar 2009')
                when 'Y' then dateadd(year, -1*(iDuration ),'24 mar 2009')
                else '24 mar 2009' end )
 
 
 
-- or maybe (untested)
 
select top 1 * from
(
select tbCustomer.iCustomerId,
       tbPurchaseItem.iPurchaseItemId,
       tbPurchaseItem.dtStart as dtStart,
       case tbProduct.cDuration
            when 'D' then dateadd(day,  (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'W' then dateadd(week, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'M' then dateadd(month,(iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
            when 'Y' then dateadd(year, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
       else tbPurchaseItem.dtStart
       end as dtFinish
 
       from tbCustomer
       inner join tbPurchase
               on tbPurchase.iCustomerId = tbCustomer.iCustomerId
       inner join tbPurchaseItem
               on tbPurchaseItem.iPurchaseId = tbPurchase.iPurchaseId
              and tbPurchaseItem.dtCancel is null
       inner join tbProduct
               on tbProduct.iProductId = tbPurchaseItem.iProductId
              and tbProduct.cDuration <> 'N'
       where (tbProduct.iProductGroupId = 437)
) sq
where dtfinish >= '24 mar 2008'
--should have an order by

Open in new window

0
 
appariCommented:
can you execute the following sql and post the results here

select max(tbPurchaseItem.dtStart) max_stdate,
min(tbPurchaseItem.dtStart) min_stdate
 from tbCustomer
       inner join tbPurchase
               on tbPurchase.iCustomerId = tbCustomer.iCustomerId
       inner join tbPurchaseItem
               on tbPurchaseItem.iPurchaseId = tbPurchase.iPurchaseId
              and tbPurchaseItem.dtCancel is null
       inner join tbProduct
               on tbProduct.iProductId = tbPurchaseItem.iProductId
              and tbProduct.cDuration <> 'N'
       where (tbProduct.iProductGroupId = 437)
0
 
appariCommented:
and also add max(iDuration ) to the select list
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
louise_8Author Commented:
Hi Appari

Thanks for your quick response
Results:
max_stdate,min_stdate,max_iDuration,
4634-06-10 00:00:00.000      2005-09-13 00:00:00.000      15

4634-06-10 00:00:00.000 is weird..
 However even when I select the top 1 record it crashes

0
 
Anthony PerkinsCommented:
>>4634-06-10 00:00:00.000 is weird..<<
That would definitely be a problem if it was smalldatetime.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
You got the error since it is identifying the date input as a char input and date value is in some other format.

Instead of << end) >= '24 mar 2009') >>

Use

end) >= cast('24 mar 2009' as datetime))

to get it resolved
0
 
louise_8Author Commented:
rrjegan17:

Thanks for your comment, it still fails.

acperkins:
The type is datetime.. however when I enter the top 1 dtStart which is '2007-04-10 00:00:00.000' instead of dtStart from the db it works fine
0
 
FVERCommented:
what is the use of the "ELSE 0" ?
Try to remove this part or replace 0 with a datetime for default value. This must be combined with rrjegan17's proposition to cast '24 mar 2009' as datetime.
0
 
LowfatspreadCommented:
isnt your problem just because you have missed the case before tbProduct.cDuration in the where clause?

e.g.
 and (convert(datetime, Dateadd(Case  tbProduct.cDuration
                                 when 'D' then "day"
                                 when 'W' then "week"  
                                 when 'M' then "month"
                                 when 'Y' then "year"
                                 end, (iDuration ),tbPurchaseItem.dtStart)
         >= '24 mar 2009')
0
 
louise_8Author Commented:
Hi All

and (convert(datetime,case tbProduct.cDuration
                                 when 'D' then dateadd(day,  (iDuration ),tbPurchaseItem.dtStart)
                                 when 'W' then dateadd(week, (iDuration ),tbPurchaseItem.dtStart)
                                 when 'M' then dateadd(month,(iDuration ),tbPurchaseItem.dtStart)
                                 when 'Y' then dateadd(year, (iDuration ),tbPurchaseItem.dtStart)
                        ELSE cast('24 mar 2009' as datetime)
                            end) >= cast('24 mar 2009' as datetime))

Gives this error:
Adding a value to a 'datetime' column caused overflow.

 and (convert(datetime, Dateadd(Case  tbProduct.cDuration
                                 when 'D' then "day"
                                 when 'W' then "week"  
                                 when 'M' then "month"
                                 when 'Y' then "year"
                                 end, (iDuration ),tbPurchaseItem.dtStart)
         >= cast('24 mar 2009' as datetime))

gives this error:
Invalid parameter 1 specified for dateadd.

When I hardcode the dtStart from the top 1 record in the query into the code it works..
eg
 and (convert(datetime,case tbProduct.cDuration
                                 when 'D' then dateadd(day,  (iDuration ),tbPurchaseItem.dtStart)
                                 when 'W' then dateadd(week, (iDuration ),tbPurchaseItem.dtStart)
                                 when 'M' then dateadd(month,(iDuration ),tbPurchaseItem.dtStart)
                                 when 'Y' then dateadd(year, (iDuration ),'2007-04-10 00:00:00.000')
                        ELSE cast('24 mar 2009' as datetime)
                            end) >= cast('24 mar 2009' as datetime))

so something to do with how the date is retrieved even though Im hard coding it as its retrieved and when I do this:
cast(tbPurchaseItem.dtStart as datetime)) I get the overflow error again

I know its probably something simple but I just cant see it :(
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Ok.. this should fix out your error, Had it tested.
One basic assumption, tbProduct.cDuration is of datatype char/ varchar and tbPurchaseItem.dtStart is of datatype Datetime.

Replace your CASE statement to the one given below.

Revert if it has produces any errors along with error message.

convert(datetime,case 
    when tbProduct.cDuration = 'D' then CONVERT(char(23),dateadd(day,  (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'W' then CONVERT(char(23),dateadd(week, (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'M' then CONVERT(char(23),dateadd(month,(iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'Y' then CONVERT(char(23),dateadd(year, (iDuration ),tbPurchaseItem.dtStart), 109)
ELSE null
 END, 109)

Open in new window

0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Replace ELSE null with ELSE '24 mar 2009' to match my solution with your earlier scenario.
0
 
Mark WillsTopic AdvisorCommented:
Couple of things....

'24 Mar 2009' is implicitly understood as a datetime (in english language databases) when used in the context of a datetime. So no problems there...

the error is as a result of doing the dateadd - so - the input field is not a legitimate datetime value. If it is a DATE or DATETIME datatype (and others in SQL2008) then it must contain a NULL value.

Also, you do not have to do a convert to datetime because the output is a datetime...


case tbProduct.cDuration
        when 'D' then dateadd(day,  (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'W' then dateadd(week, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'M' then dateadd(month,(iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'Y' then dateadd(year, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
else tbPurchaseItem.dtStart
end as dtFinish
0
 
louise_8Author Commented:
Hi,

Unfortunately using both messages I get the error again.
rrjegan17:
and convert(datetime,case
    when tbProduct.cDuration = 'D' then CONVERT(char(23),dateadd(day,  (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'W' then CONVERT(char(23),dateadd(week, (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'M' then CONVERT(char(23),dateadd(month,(iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'Y' then CONVERT(char(23),dateadd(year, (iDuration ),tbPurchaseItem.dtStart), 109)
ELSE '24 mar 2009'--cast('24 mar 2009' as datetime)
 END, 109) >= '24 mar 2009'--cast('24 mar 2009' as datetime)

gives the same error :(
Server: Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.

Mark
and convert(datetime,case tbProduct.cDuration
        when 'D' then dateadd(day,  (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'W' then dateadd(week, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'M' then dateadd(month,(iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
        when 'Y' then dateadd(year, (iDuration ),isnull(tbPurchaseItem.dtStart,'19000101'))
else '24 mar 2009' --cast('24 mar 2009' as datetime)
end) >= '24 mar 2009' --cast('24 mar 2009' as datetime)

does the same :(
 Server: Msg 517, Level 16, State 1, Line 1
Adding a value to a 'datetime' column caused overflow.

For both queries, theres an error
whether I include cast('24 mar 2009' as datetime) or not
and if I hardcode cDuration & iduration..
Its going into the Y part of the case statement

If I take the case out of the where part and say:
and tbPurchaseItem.dtStart >= '24 mar 2009' it works and dtStart is 2017-08-04 00:00:00.000

is this annoying or what? :)
0
 
Mark WillsTopic AdvisorCommented:
Can change the date in that last one ;)
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
A small mistake..
The one below will solve your problem.
convert(datetime,case 
    when tbProduct.cDuration = 'D' then CONVERT(char(23),dateadd(day,  (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'W' then CONVERT(char(23),dateadd(week, (iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'M' then CONVERT(char(23),dateadd(month,(iDuration ),tbPurchaseItem.dtStart), 109)
    when tbProduct.cDuration = 'Y' then CONVERT(char(23),dateadd(year, (iDuration ),tbPurchaseItem.dtStart), 109)
ELSE convert(char(23),convert(datetime,'24 mar 2009',106),109)
 END, 109)

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
hi rrjegan17, shouldn't be needed - if you take out all the other case statement you would have ended up with convert(datetime,'24 mar 2009')  which will work (in an english speaking database) - think it is down in the WHERE clause.
0
 
louise_8Author Commented:
Hi Guys

Mark you're a genius :)
Your final code worked perfect.. although not sue why can you explain?

I have awarded you the full 100.
However I increased the points and gave rrjegan17 20 points for working on this from day 1.

Thanks guys
0
 
Mark WillsTopic AdvisorCommented:
There is always a temptation to convert datetimes to datetimes. And if you are already dealing with recognised dates, then leave it that way and focus on the date criteria that you have on hand.

in the case of the "inline / select" case statement, we are doing a series of dateadd's so the only thing we have to worry about is the ELSE still needs to be a datetime, and if the date we are adding to is NULL - everything else is automatically handled as a recognised datetime.

in the case of the "where" case statement, we are far better off leaving the table columns alone - particularly if they are part of an index. So, it became the "opposite" problem of reducing our parameter date, and still only ever handling a recognised date format (but only in english speaking databases).

does that make any sense ?
0
 
louise_8Author Commented:
so you mean adding to the table field dtStart opened us up to problems in the where clause because it was no longer a recognised date formula

what way was it being interpreted then?
0
 
Mark WillsTopic AdvisorCommented:
Ummmm, not really, but nearly...

Consider the previous with problems. We are doing dateadd calcs quite happily with date fields - as evidenced in the "inline" query, so what is the difference ? Obviously there are some conditions not being met. And that is what we have to focus on. Not what is written, but how the data is going to be presented based on various conditions.

The first one is "what if cduration is not one of those values" then we get a string of '23 mar 2009' - not in a date context just yet.
The second one is "what if dtstart does not have a real date" then we need to do that isnull business - the only other choice for a datetime field.

Now the "biggie" is "if i take all the different possible conditions in the case, am I always left with the same datatype" and the answer is no.

The lateral approach when using CASE statements is make very sure that the datatype is preserved in any condition.

The easiest way for that to happen is to use the data that we do know about. For example in the inline query, there is no need to convert to datetime, because all possible conditions will yield a datetime. So, that one can be simplified big time, by simply accommodating a possible NULL condition, and really and truly we did not have to do that if a NULL datetime was a legitimate answer anyway (the dateadd would yield a NULL), except that it loses it's datatype because it is the result of a calculated field.

For the "WHERE" case statement, it was a little different. The lateral approach was to make sure the resulting string / resulting answer could be implicitly converted (if not exactly expressed) as a datetime because it is being compared explicitly to a datetime and so has that context. That is why 'dd MMM yyyy' works as a string - because it is always used in the context of a datetime function ( the other ones are the ISO8601 standards of yyyymmdd and yyyy-mm-dd and language independant).

So, to avoid the unknowns , and to help the query optimiser, we elected not to do the date arithmetic on the data column, but, to do it on the variable part of the where clause. So, that actually worked in our favour for more than one reason. The resulting output of any part of that case statement can be understood as a datetime, and no longer needs converting.

So, you are correct in saying "it was no longer a recognised date formula" but not because adding to dtstart (which is the 'not really' part of my opening comment).  


0
 
louise_8Author Commented:
Thanks Mark..

Making more sense now, appreciate your time!!

Louise
0
 
Mark WillsTopic AdvisorCommented:
It is kind of subtle, and sometimes hard to see the difference. What we write as code is so very heavily dependant on what we cannot always see (ie the data), that what appears to be good and reasonable code comes undone by the data setting up a series of conditions for which the code does not quite behave as expected. Datetimes are always a bit of a challenge - we possibly get more of those in EE in all their different guises than any other datatype. The other biggie is managing the NULL condition. It can mean that rows are bypassed, or data is excluded from aggregations, and even lead to batches crashing where it will working interactively.

Have a look below, open a new query window and paste the code and run. Might not be the best examples, but hopefully exemplifies some of the above dialogue...

-- by way of possibly a poor example of datetime results
 
declare @d datetime
declare @v char(1)
declare @iduration int
 
set @d = NULL
set @v = 'Q'
set @iduration = 15
 
select getdate() as a_real_datetime
       , @d as our_datetime
       , '23 Mar 2009' as a_string_date_style_106
       , dateadd(year, -1*(@iDuration ),@d) as computed_datetime
       , case @v
         when 'D' then dateadd(day,  -1*(@iDuration ),@d)
         when 'W' then dateadd(week, -1*(@iDuration ),@d)
         when 'M' then dateadd(month,-1*(@iDuration ),@d)
         when 'Y' then dateadd(year, -1*(@iDuration ),@d)
         else '23 Mar 2009' end  as our_where_case_statement
 
-- notice the last three columns of the above query - how does one potentially manage all three different results as the same datatype ?
 
 
-- and an over simplification example of the differences if you manage the NULL condition
 
Select count(*),avg(score),avg(isnull(score,0)),sum(score),count(score), count(distinct score), count(distinct isnull(score,0))
from
(select 10.0 as score union all
 select 10.0 as score union all
 select NULL as score union all
 select 10.0 as score union all
 select 10.0 as score) s

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
Oh, on that last one, have a look at the messages tab - that 'warning' message can become a fatal error in some batches...
0
 
louise_8Author Commented:
Im understanding you now..

and that data was part of a batch that got shifted from SS5 to SS8 last year.. so I can see how the dates may have been in different formats, that and different import programs

Thanks so much!
0
 
Mark WillsTopic AdvisorCommented:
Absolutely - SS8 has a few difference with dates, and generally much much better than SS5. Like the DATE construct can have date ranges from 1/1/1 - a date which was never previously permitted.

Anyway, hope that helped, and I will sign out now...

Cheers,
Mark Wills
0
 
louise_8Author Commented:
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.