Adding a value to a 'datetime' column caused overf

• camlern

  SSC-Addicted

  Points: 425

  More actions

  December 11, 2003 at 9:31 am

  #81739

  I have a view and a table that I want to summarize the values in the view based on an ID field and date field in the table.

  My statement is as follows:

  SELECT dbo.Progress_Notes.Consumer_Id,

  dbo.Progress_Notes.Progress_Notes_Id,

  COUNT(dbo.vw_Consumer_Assessments.Assessment_ID) AS NumAssessments, SUM(dbo.vw_Consumer_Assessments.Submitted) AS NumAccepted, SUM(dbo.vw_Consumer_Assessments.Accepted) AS NumSubmitted

  FROM dbo.vw_Consumer_Assessments

  INNER JOIN dbo.Progress_Notes

  ON dbo.vw_Consumer_Assessments.ConsumerID = dbo.Progress_Notes.Consumer_Id

  AND dbo.vw_Consumer_Assessments.Date_of_Assessment >=

  DATEADD(m, - 6, dbo.Progress_Notes.Date_)

  AND dbo.vw_Consumer_Assessments.Date_of_Assessment <= dbo.Progress_Notes.Date_

  GROUP BY dbo.Progress_Notes.Progress_Notes_Id, dbo.Progress_Notes.Consumer_Id

  I keep getting this error:

  Server: Msg 517, Level 16, State 1, Line 95

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

  Any ideas on what I have wrong?

  Thanks,

• David Burrows

  SSC Guru

  Points: 65144

  More actions

  December 12, 2003 at 6:12 am

  #485923

  Check your data (dbo.Progress_Notes.Date_) the lowest date allowed (SQL7) is '1753-01-01'. Subtracting from a date that produces a value less than this will cause the error.

  Far away is close at hand in the images of elsewhere.
  Anon.