Why does this cause an ArithmeticException in C# when SQLPlus is all ok
I have a view connecting 4 tables
CREATE VIEW BookCopyInfo
AS
SELECT
bc.BookCopyID, b.BookTitle, m.FirstName || ' ' || m.LastName AS BorrowedBy,
l.expectedReturnDate, (SYSDATE - l.expectedReturnDate) AS NoOfDaysLate
FROM Book b, Member m, Lending l, Bo开发者_开发技巧okCopy bc
WHERE b.BookID = bc.BookID AND l.MemberID = m.MemberID
AND l.BookCopyID = bc.BookCopyID
There is a small date arithmetic going on that finds how many days was a book late
(SYSDATE - l.expectedReturnDate)
When i do a SELECT * FROM BookCopyInfo
, i get rows like
4 | Human Computer Interaction | Alan Paul | 10-JUL-10 | -13.642292
So it is correct and -13 is the correct answer actually.
DESC BookCopyInfo
returns
Name Null? Type
----------------------------------------- -------- ---------------
BOOKCOPYID NOT NULL NUMBER(38)
BOOKTITLE NOT NULL VARCHAR2(100)
BORROWEDBY VARCHAR2(126)
EXPECTEDRETURNDATE NOT NULL DATE
NOOFDAYSLATE NUMBER(38)
However in C#
DataTable dtBookInfo = new DataTable();
da = new OracleDataAdapter("SELECT * FROM BookCopyInfo", con);
da.Fill(dtBookInfo);
catches an exception in the da.Fill line
OverflowException was unhandled by user code.
Arithmetic operation resulted in an overflow.
Why does it work fine in SQLPlus but fails in C#? :S
I believe it has to with SQL-Number data type. You can try the following option..
1) .... Round((SYSDATE - l.expectedReturnDate),2 ) ...
2) Convert the data type of "NoOfDaysLate" to integer.
IF you are just interested in some part of the difference between the two dates, then you should use DateDiff instead...
Eg: DATEDIFF(yy, startDate, endDate) YearsPassedSinceStart
returns the no. of years passed since the start date.
Similarly to find number of months, use 'm' as the datepart or 'd' to determine no. of days.
精彩评论