> I was innocently looking through my database for events that happened
> during office hours -- I had to add the time zone and daylight savings
> time values to the date value, as they happened all over the world
> throughout the year.
>
> And I kept getting this error.
>
> Server: Msg 517, Level 16, State 1, Line 8
> Adding a value to a 'datetime' column caused overflow.
>
> It turned out that what was causing this was a date value '1600-11-23
> 22:32:24.000'. Now, that's not valid, but it was in the database. Books
> Online says datetime, which is the type of the column, holds
>
> Date and time data from January 1, 1753 through December 31, 9999, to
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). Values are rounded to increments of
> .000, .003, or .007 seconds....
>
> So, I didn't think the table would be able to hold that value. But, it
> totally can. And did! The newsgroup didn't have anything helpful when I
> was trying to understand the error, so I'm adding this.
>
> SELECT datefield FROM tablename works great
> SELECT dateadd(minute, 0, datefield) FROM tablename blows up with the
> above error.

news:1156537830.484590.170500@m73g2000cwd.googlegroups.com...
>I was innocently looking through my database for events that happened
> during office hours -- I had to add the time zone and daylight savings
> time values to the date value, as they happened all over the world
> throughout the year.
>
> And I kept getting this error.
>
> Server: Msg 517, Level 16, State 1, Line 8
> Adding a value to a 'datetime' column caused overflow.
>
> It turned out that what was causing this was a date value '1600-11-23
> 22:32:24.000'. Now, that's not valid, but it was in the database. Books
> Online says datetime, which is the type of the column, holds
>
> Date and time data from January 1, 1753 through December 31, 9999, to
> an accuracy of one three-hundredth of a second (equivalent to 3.33
> milliseconds or 0.00333 seconds). Values are rounded to increments of
> .000, .003, or .007 seconds....
>
> So, I didn't think the table would be able to hold that value. But, it
> totally can. And did! The newsgroup didn't have anything helpful when I
> was trying to understand the error, so I'm adding this.
>
> SELECT datefield FROM tablename works great
> SELECT dateadd(minute, 0, datefield) FROM tablename blows up with the
> above error.
>

> There are several ways to get "impossible" data into some datatypes.  For
> SQL 2005, Microsoft has added a new option to DBCC CHECKDB and DBCC
> CHECKTABLE called DATA_PURITY to check for this problem.  There is a KB
> article about it at
>
> http://support.microsoft.com/kb/923247/en-us
>
> Even if you are on SQL 2000, this will be a good article for you because it
> discusses some of the ways this could have happened and what you might be
> able to do about it and some T-SQL commands that you might be able to use to
> find if you have other occurances of this in other datetime columns in your
> database.
>
> Tom
>
> <ionFree***@gmail.com> wrote in message
> news:1156537830.484590.170500@m73g2000cwd.googlegroups.com...
> >I was innocently looking through my database for events that happened
> > during office hours -- I had to add the time zone and daylight savings
> > time values to the date value, as they happened all over the world
> > throughout the year.
> >
> > And I kept getting this error.
> >
> > Server: Msg 517, Level 16, State 1, Line 8
> > Adding a value to a 'datetime' column caused overflow.
> >
> > It turned out that what was causing this was a date value '1600-11-23
> > 22:32:24.000'. Now, that's not valid, but it was in the database. Books
> > Online says datetime, which is the type of the column, holds
> >
> > Date and time data from January 1, 1753 through December 31, 9999, to
> > an accuracy of one three-hundredth of a second (equivalent to 3.33
> > milliseconds or 0.00333 seconds). Values are rounded to increments of
> > .000, .003, or .007 seconds....
> >
> > So, I didn't think the table would be able to hold that value. But, it
> > totally can. And did! The newsgroup didn't have anything helpful when I
> > was trying to understand the error, so I'm adding this.
> >
> > SELECT datefield FROM tablename works great
> > SELECT dateadd(minute, 0, datefield) FROM tablename blows up with the
> > above error.
> >