Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
matthewk
Starting Member
2 Posts |
 Posted - 2005-08-12 : 17:01:39
|
| I have a table containing a column of Int(4), and there is normal date values that can be converted, such as 365 which equals 1/1/1901. However, the column also has values such as -115784. When my query hits that record, I get the "Arithmetic overflow error converting expression to data type smalldatetime" or "Arithmetic overflow error converting expression to data type datetime". Any help in identifying what date -115784 represents (sometime before 1/1/1900) and how to report it and make the query work would be greatly helpful. |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-08-12 : 17:16:27
|
| Converting integers to datetime data is the equivalent of adding or subtracting the number of days from 1/1/1900. Since SQL Server does not recognize dates before 1/1/1753, the smallest negative integer it would be able to recognize would be -53690. To convert -115784, you would have to find what date would result after subtracting that many days from 1/1/1900. |
 |
|
|
nosepicker
Constraint Violating Yak Guru
366 Posts |
Posted - 2005-08-12 : 17:21:11
|
| I just did a quick extrapolation, and I think -115784 would be 12/28/1552 (if I am correct in assuming that the year 1600 is not a leap year). |
|
|
|
matthewk
Starting Member
2 Posts |
Posted - 2005-08-12 : 17:30:29
|
| LOL. Thank you very much and understand there isn't a native conversion in SQL to handle this. The data is getting exported using Bulk copy and put into an Excel document for reporting. Maybe I will add some more VBA to convert these dates in my report generator. |
|
|
|
|
|
|
Converting Negative Int to Date String