Adding a Value to a 'date' Column caused an overflow??

Article
12/20/2012

Question

_{Thursday, December 20, 2012 5:40 AM}

Hi all,

When I have below CASE expression in my Select query, query runs for a while and returns the an error as below:

CASE
          WHEN transaction_type IN ( 'Surrender' ) AND Tran_days <> 0 THEN CONVERT(INT, CONVERT(CHAR(8), DATEADD(DD, CONVERT(INT,M.net_premium /( CAP.net_premium /365 )), M.end_date), 112))
       END AS   New_Surr_ID

Error message:

Msg 517, Level 16, State 3, Line 20

Adding a value to a 'date' column caused an overflow

Any Idea??

Mira

All replies (14)

_{Friday, December 21, 2012 4:04 AM ✅Answered}

Your case statement is apparently either adding so many days to M.end_date that the result is greater than Dec 31, 9999 or subtracting so many days that the result is less than Jan 1, 1753 (if your M.end_date datatype is datetime or smalldatetime) or less than Jan 1, 0001 (if your M.end_date is datetime2 or date). Try running

Select m.net_premium, CAP.net_premium, M.end_date, <any other columns you need to identify the rows where this data comes from (like primary keys)>,
   CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) As DaysToAdd,
   DateDiff(dd, M.end_date, '99991230') As MaxAllowableDaysToAdd,
   DateDiff(dd, M.end_date, '17530101') As MinAllowableDaysToAdd
FROM dbo.factFinTran as M
    LEFT OUTER JOIN #capturevals AS CAP ON (M.policy_id = CAP.policy_id AND CAP.net_premium <> 0)
    where M.net_premium <> 0  
    and (CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) > DateDiff(dd, M.end_date, '99991230') 
      or CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) < DateDiff(dd, M.end_date, '17530101'))

to see if it returns any rows. If so, those rows may well be causing you your problem.

Tom

_{Friday, December 21, 2012 4:05 AM ✅Answered}

Try

WHEN m.transaction_type IN ( 'Surrender' ) THEN DATEADD(DD, case CONVERT(INT,ISNULL(M.net_premium,0) /( case ISNULL(CAP.net_premium,0) when 0 then 1 else ISNULL(CAP.net_premium,0) end /365 )) when null then 0 end, M.end_date)

Many Thanks & Best Regards, Hua Min

_{Thursday, December 20, 2012 5:45 AM}

Are you sure, you are getting error on the CASE statement? Could you please show us the exact query and the table structure where you trying to insert the same?

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Thursday, December 20, 2012 6:03 AM}

I think the date range that you specified is invalid. Please post the ddl along with a query so that we are able to reproduce the issue.

Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

_{Thursday, December 20, 2012 6:26 AM}

You can run the same query to see the value of "CONVERT(INT,M.net_premium /( CAP.net_premium /365 ))". Probably it is returning very big value that causes the error.

Many Thanks & Best Regards, Hua Min

_{Thursday, December 20, 2012 6:31 AM}

Hi,

The issue is because the CONVERT(INT,M.net_premium /( CAP.net_premium /365) when u add to M.end_date, it exceeds the date '9999-12-31' so its giving error

proof:

select dateadd(dd,convert(int,2917202),getdate())
select dateadd(dd,convert(int,2917203),getdate())

Regards,

Vipin

Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

_{Friday, December 21, 2012 2:28 AM}

Hi All

Thank you for all posts, but I still have the issue...

1. FIRST CASE STATEMENT works fine.

2. SECOND CASE STATEMENT does not work .. give the error as

Msg 517, Level 16, State 3, Line 19
Adding a value to a 'date' column caused an overflow.

3. CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) RETURNS SOME null VALUES.

4. I am using TEMP tables, does it have any impact ??

5. Query works fine for few policies,,, but when try to execute for all it creates problems

Here is the code:

use DW

        IF OBJECT_ID('tempdb..#capturevals') IS NOT NULL
            BEGIN
                DROP TABLE #capturevals
            END;

Set up temp table for handling Surrender Net_Premium issues ## 20/12/2012
        SELECT fin_tran_id
               ,policy_id
               ,net_premium
        INTO    #capturevals
        FROM    factFinTran as F1
        WHERE   fin_tran_id =
                (select Max(fin_tran_id)
                  from factFinTran as F2
                   where F1.policy_id = F2.policy_id AND F2.transaction_type <> 'Surrender' );

      SELECT
       M.fin_tran_id
       , M.policy_id
       ,M.end_date
       ,CASE   -- FIRST CASE STATEMENT
          WHEN transaction_type IN ( 'Surrender' ) THEN CONVERT(INT,M.net_premium /( CAP.net_premium /365 ))
       END AS   Days
       --###############
       ,CASE    -- SECOND CASE STATEMENT ?? PROBLEMS??
             WHEN m.transaction_type IN ( 'Surrender' ) AND CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) IS NOT NULL THEN DATEADD(DD, CONVERT(INT,M.net_premium /( CAP.net_premium /365 )), M.end_date) -- ## PROBLEM WITH THIS STATEMET
           --WHEN m.transaction_type IN ( 'Surrender' ) AND CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) IS NOT NULL THEN DATEADD(DD, 273199, M.end_date) -- THIS STATEMENT WORKS FINE
       END AS   AddDates
       FROM dbo.factFinTran as M
   LEFT OUTER JOIN #capturevals AS CAP ON (M.policy_id = CAP.policy_id AND CAP.net_premium <> 0)
   where M.net_premium <> 0 --AND M.policy_id =3218

Thanks

Mira

_{Friday, December 21, 2012 2:52 AM}

Hi All

Thank you for all posts, but I still have the issue...

2. SECOND CASE STATEMENT does not work .. give the error as

Msg 517, Level 16, State 3, Line 19
Adding a value to a 'date' column caused an overflow.

3. CONVERT(INT,M.net_premium /( CAP.net_premium /365 )) RETURNS SOME null VALUES.

Use this

CONVERT(INT,isnull(M.net_premium,0) /( isnull(CAP.net_premium,0) /365 ))

and make sure the result of it is not too big.

Many Thanks & Best Regards, Hua Min

_{Friday, December 21, 2012 3:22 AM}

Thanks...

Changes as

DATEADD(DD, CONVERT(INT,ISNULL(M.net_premium,0) /( ISNULL(CAP.net_premium,0) /365 )), M.end_date)

Now I am getting below error

Msg 8134, Level 16, State 1, Line 19
Divide by zero error encountered.

Please help...

Mira

_{Friday, December 21, 2012 3:31 AM}

Try

DATEADD(DD, CONVERT(INT,ISNULL(M.net_premium,0) /( case ISNULL(CAP.net_premium,0) when 0 then 1 else ISNULL(CAP.net_premium,0) end /365 )), M.end_date)

Many Thanks & Best Regards, Hua Min

_{Friday, December 21, 2012 3:42 AM}

Hi HuaMin

Really really appreciate your support, but situation back to previous state::

Msg 517, Level 16, State 3, Line 19
Adding a value to a 'date' column caused an overflow.

Changed line::

WHEN m.transaction_type IN ( 'Surrender' ) THEN DATEADD(DD, CONVERT(INT,ISNULL(M.net_premium,0) /( case ISNULL(CAP.net_premium,0) when 0 then 1 else ISNULL(CAP.net_premium,0) end /365 )), M.end_date)

Any idea??

_{Friday, December 21, 2012 3:50 AM}

Try to see the value of

CONVERT(INT,ISNULL(M.net_premium,0) /( case ISNULL(CAP.net_premium,0) when 0 then 1 else ISNULL(CAP.net_premium,0) end /365 ))

really is.

Many Thanks & Best Regards, Hua Min

_{Friday, December 21, 2012 4:01 AM}

Hi

This returns numeric values and NULLs.. ( 22332, 122331, 123, -33, -11, NULL, NULL......)

Mira

_{Friday, December 21, 2012 4:22 AM}

Now I can smile....

Thnak you everybody, in particular.. HuaMin and Tom Cooper, you guys made my day!!

Found the error>>CONVERT(INT,ISNULL(M.net_premium,0) /( case ISNULL(CAP.net_premium,0) when 0 then 1 else ISNULL(CAP.net_premium,0) end /365 )),, generate some values as -4283902, -13477592.. which causes the problem....

I need to fix these troublesome values before running the query....

Anyway thank you everybody once again and have a Merry Christmas and Happy New Year....

Mira