CODEWITHSUNDEEP
sqlsql-server
user2688077
user2688077

Reputation: 1

Msg 8115, Level 16, State 2, Line 24 (Arithmetic overflow error converting expression to data type datetime )

CREATE TABLE #tmpCustomers(
[CustomerID] [int] NOT NULL,
[CustomerCode] [varchar](20) NULL,
[CustomerName] [varchar](128) NULL,
[LeftCount] [int] NULL,
[RightCount] [int] NULL,
[CreationDate][datetime]null,
)

DECLARE Customer_Cursor CURSOR FOR
SELECT customerid
FROM Customers
OPEN Customer_Cursor;

declare @left  int
declare @right int
declare @customerid int

FETCH NEXT FROM Customer_Cursor into @customerid
WHILE @@FETCH_STATUS = 0
BEGIN
  EXEC dbo.CountChildren @customerId,@left out,@right out

    insert into #tmpCustomers 
    select customerId,[CustomerCode],[CustomerName],(CONVERT(NUMERIC(38,2), Creationdate)),@left,@right from Customers where CustomerID=@customerid
   FETCH NEXT FROM Customer_Cursor into @customerid;
END;

select *, case when  leftcount>RightCount then RightCount else LeftCount end as Pairs from #tmpCustomers a
drop table #tmpCustomers
CLOSE Customer_Cursor;
DEALLOCATE Customer_Cursor;

Here is code , this code is giving following error:

Msg 8115, Level 16, State 2, Line 24
Arithmetic overflow error converting expression to data type datetime.

Please guide me how to set it.

Upvotes: 0

Views: 6996

Answers (1)

D Stanley
D Stanley

Reputation: 152566

I see two things that look wrong:

  1. You are converting a datetime to a numeric value, which gives you the number of days (and fractional days) since 1900-01-01. I don't see why that's necessary in your code.
  2. The columns don't line up properly in your INSERT statement. You are inserting a NUMERIC(18,2) value (due to the CONVERT) into an integer field and an int value into a DATETIME field. That's probably why you're seeing the wrong values for CreationDate in your results.

I suspect you want something like:

INSERT INTO #tmpCustomers 
SELECT 
    customerId,
    [CustomerCode],
    [CustomerName],
    @left,
    @right, 
    Creationdate
FROM Customers 
WHERE CustomerID=@customerid

Upvotes: 0

Related Questions