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

• sqlbidev

  Ten Centuries

  Points: 1195

  More actions

  July 3, 2015 at 4:49 pm

  #1810253

  The table below is the result of the modifed script. This works fine for one a row with one or more subid.

  subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate

  2000 2006-02-09 2006-02-09 2006-02-04 0 2000_0 2006-02-27

  3000 2007-04-07 2007-04-07 2007-04-09 1 2000_1 2006-03-27

  4000 2008-06-02 2008-06-02 2008-06-06 2 2000_2 2006-04-27

  but i had made a mistake, i forgot to mention that i want to iterate for number of days

  Datediff(days,prodgdate,prodenddate) so for subid i have 5 days of difference and the result should look like this in the end

  subID prodgdate salesdate prodendDate RowNum Subrowlog Subrowdate

  2000 2006-02-09 2006-02-09 2006-02-04 0 2000_0 2006-02-04

  2000 2006-02-09 2006-02-09 2006-02-04 1 2000_1 2006-03-04

  2000 2006-02-09 2006-02-09 2006-02-04 2 2000_2 2006-04-04

  2000 2006-02-09 2006-02-09 2006-02-04 3 2000_3 2006-05-04

  2000 2006-02-04 2006-02-09 2006-02-04 5 2000_4 2006-06-04

  3000 2007-04-07 2007-04-07 2007-04-09 0 2000_0 2006-04-07

  3000 2007-02-27 2007-04-07 2007-04-09 1 2000_1 2006-05-07

  This is the script:

  select *, CAST(subID as CHAR(4)) + '_' + CAST(RowNum as char(2)) subscriptionrowlog

  -- ,DATEADD(mm, RowNum, prodgdate) subscriptionrowdate

  ,CASE WHEN RowNum<= DATEDIFF(DAY, salesdate, prodendDate) THEN

  DATEADD(mm, RowNum, salesdate) --DATEADD(mm, RowNum, CONVERT(VARCHAR(10),salesdate,112))--DATE OVERFLOW???

  ELSE '1900-01-01'

  END AS subscriptionrowdate

  from (

  select subID, prodgdate, salesdate, prodendDate,

  ROW_NUMBER() over(Partition by subID order by prodgdate)-1 RowNum

  from @t

  ) a