Link to home
Start Free TrialLog in
Avatar of running32
running32

asked on

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
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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

Avatar of aledev
aledev

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.
Avatar of running32

ASKER

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
have you tried this?

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

'1900-01-01 00:00:00'
NO the default date looks like 3/2/1753 or NULL
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You might look for this to find the problem data:

Select * from yourtable
  Where IsDate(p.LASTMISSEDAPPT) = 0
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
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.