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

Question

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.

What is the data type of CreationDate and why are you converting it to NUMERIC. The problem could simply be that you are not specifying the columns when inserting into #tmpCustomers. — T I
i want creatiodate in output , code was giving correct output before adding creationdate ... so problem is in creationdate... — user2688077
CreationDate 1902-02-15 00:00:00.000 ...... year value is not correct in output — user2688077

D Stanley D Stanley · Accepted Answer · 2013-08-21T15:51:45

I see two things that look wrong:

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.
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

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

1 Answers