• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1164
  • Last Modified:

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

i am getting the error "Adding a value to a 'datetime' column caused overflow."   when I try and run this statement.  DATEADD(dd, - 365, p.LASTMISSEDAPPT) < pl.PLDATE.  how can I change DATEADD(dd, - 365, p.LASTMISSEDAPPT) to avoid this.

Thanks
0
running32
Asked:
running32
  • 4
  • 4
  • 2
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what are values in the 2 fields? you might hit min value of date ...what about:
 p.LASTMISSEDAPPT < dateadd(dd, 365, pl.PLDATE)


resp:

p.LASTMISSEDAPPT < dateadd(year, 1, pl.PLDATE)

Open in new window

0
 
aledevCommented:
Waht type is p.LASTMISSEDAPPT?

If p.LASTMISSEDAPPT is a float "representing" a date, then this value is likely
to be too high. If it exceeds 2936549 you will get an out or range error
(or similar error). If it represents a date, you should cast it to a
datetime.
0
 
running32Author Commented:
Example of a field is the value of the fields could be 9/7/2010.  It still does it when I cast the value as datetime 101.  thanks
0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
aledevCommented:
have you tried this?

DATEADD(year, -1, p.LASTMISSEDAPPT)
0
 
running32Author Commented:
yes, still gives me the same error.  Thanks
0
 
aledevCommented:
There are any rows in your data table with this value in that field?

'1900-01-01 00:00:00'
0
 
running32Author Commented:
NO the default date looks like 3/2/1753 or NULL
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
which is the minimum year value (1753);
http://msdn.microsoft.com/en-us/library/ms187819.aspx

going 1 year back will indeed result in that error.
please try what I suggested first, changing to do the "dateadd(year,1 ,... )"
0
 
mastooCommented:
You might look for this to find the problem data:

Select * from yourtable
  Where IsDate(p.LASTMISSEDAPPT) = 0
0
 
aledevCommented:
maybe the minimum SQL DateTime Value is 1/1/1753. Substruct one year to fields with the value you have mentioned ("3/2/1753") throw the exception
0
 
running32Author Commented:
angelIII, I tried your suggestion but it didn't work still gave the error

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

0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now