Solved

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

Posted on 2009-03-30
27
4,498 Views
Last Modified: 2013-11-05
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

0
Comment
Question by:louise_8
  • 9
  • 9
  • 4
  • +4
27 Comments
 
LVL 39

Expert Comment

by:appari
ID: 24025297
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
 
LVL 39

Expert Comment

by:appari
ID: 24025305
and also add max(iDuration ) to the select list
0
 

Author Comment

by:louise_8
ID: 24025403
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 24025468
>>4634-06-10 00:00:00.000 is weird..<<
That would definitely be a problem if it was smalldatetime.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24025484
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
 

Author Comment

by:louise_8
ID: 24025612
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
 
LVL 6

Expert Comment

by:FVER
ID: 24026549
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
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 24032080
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
 

Author Comment

by:louise_8
ID: 24034716
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24036469
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24036477
Replace ELSE null with ELSE '24 mar 2009' to match my solution with your earlier scenario.
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24043308
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
 

Author Comment

by:louise_8
ID: 24044907
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 51

Accepted Solution

by:
Mark Wills earned 100 total points
ID: 24045884
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24045890
Can change the date in that last one ;)
0
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 20 total points
ID: 24046179
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046222
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
 

Author Closing Comment

by:louise_8
ID: 31564666
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046357
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
 

Author Comment

by:louise_8
ID: 24046470
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24046745
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
 

Author Comment

by:louise_8
ID: 24055560
Thanks Mark..

Making more sense now, appreciate your time!!

Louise
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24055752
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24055762
Oh, on that last one, have a look at the messages tab - that 'warning' message can become a fatal error in some batches...
0
 

Author Comment

by:louise_8
ID: 24056115
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 24056393
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
 

Author Comment

by:louise_8
ID: 24093477
Thanks again
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now