"Arithmetic overflow error converting expression to data type nvarchar" when trying to get datediff

Question

Level1  Level2      createdate        CCcreatedate      datedifference

23    |   3   |  10/24/2014 17:07   | 10/24/2014 17:07  
24    |   3   |  10/24/2014 23:48   | 10/25/2014 17:07  
25    |   3   |  10/25/2014 9:57    | 10/26/2014 17:07  
26    |   1   |  10/26/2014 17:49   | 10/27/2014 17:07  
27    |   1   |  10/16/2014 12:53   | 10/28/2014 17:07  
28    |   1   |  10/16/2014 12:32   | 10/29/2014 17:07  
29    |   2   |  10/16/2014 13:58   | 10/30/2014 17:07  
30    |   2   |  10/16/2014 16:40   | 10/31/2014 17:07  
31    |   2   |  10/16/2014 20:28   | 11/1/2014 17:07   
32    |   2   |  10/15/2014 17:09   | 11/2/2014 17:07   

I have a table with 5 columns, 2 of which are dates. One declared as nvarchar(createdate) and the other as datetime (CCcreatedate) I am trying to get the difference between the 2 dates to show up on a 3rd column (datedifference) which is also nvarchar. Getting an error stating

Arithmetic overflow error converting expression to data type nvarchar.

when i use the query below.

update table
set datedifference = 
datediff(minute,CONVERT(nvarchar,CAST([createdate] as datetime)),CONVERT(nvarchar,CAST([CE_activity_create_Date]as datetime)))

I am trying find the difference between the 2 dates so I can later get the distinct (Level 2) with minimum datedifference

Solution

You probably need to make your datedifference column larger. I just got the same error you did when I ran the following code:

declare @T nvarchar(3)
Select @T = DateDiff(minute, '10/15/2014 17:09','11/2/2014 17:07')

The difference, in minutes is 25,918. If your nvarchar column is too small, you get the error. I would encourage you to change the data type of your datedifference column to int instead of nvarchar.

You can use this code to see how large (or small) or column is:

Select max_length 
from   sys.columns 
where  name = 'datedifference' 
       and object_id = object_id('YourTableNameHere')