Overflow

[andreis]

Hi:

While running:
select dateadd(d,History.His__Date-693596, '01/01/1900') from History,

I get Error Message 517:
Adding a value to a 'datetime' column caused overflow.
The same error on another machine.
SET ARITHABORT OFF didn't help.
Any hints how to deal with it?
Thanks
Andrei

 

[tlbroadbent]

What is the content of History.His__Date? If it is less than Jan 2 3652 subtracting 693596 will make the date less than Jan 1 1753 which is the earliest date that can be stored in a SQL Server DATETIME column. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[andreis]

Terry:
No, the dates are 5-10 years ago, and the query handles well up to 1500 records, but after that the overflow happens. Thanks for the answering anyway.
Andrei

 

[tlbroadbent]

I don't understand your formula. You are subtracting 693596 from a date and adding the result to Jan 1 1900. It doesn't compute. What do you really want to do?

Dateadd is used to add or subtract a number of days (or years, months, hours, etc.) from a date. You can, for example, add 500 days to the history date.

Dateadd(d,500,History.His__Date) Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[andreis]

Terry:
I'm doing data conversion.
Magic represents the date as an integer. All dates are a numeric integer. The integer represents the number of days from 01/01/0001. 693596 is the number for 01/01/1900, so if you take the following formula:

01/01/1900 + x Days where x = [DateField]- 693596

That should convert all dates.
Therefore, my select is:
select dateadd(d,History.His__Date-693596, '01/01/1900') from History

Thanks.
Andrei

 

[tlbroadbent]

Have you run a query to validate the data in the "Date" column? Obviously, there is an overflow problem which indicates something is out of range. A simple query like the following would pinpoint the data that is in error or verify your data and allow you to focus on some other source of the problem.

Select * From History
Where His__Date<693596 Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[andreis]

Terry:
You got it! 30 out of 2 million records had value 0.
Can't tell how grateful I am for you help.
Andrei