Strange behaviour for DATEADD-months ssrs 2008. adding N months in LastDay of Month gives incorrect result in some case

• virenkumar.patel

  SSC Veteran

  Points: 281

  More actions

  June 20, 2013 at 2:31 am

  #294888

  Hi,

  I am using SSRS 2008 R2. I want to get "Next N months" date range. I have used this SSRS Expression for end date: "=DateAdd("m",4, DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Today), Month(Today), 1)))))". It is working fine for many scenarios but failed for case when we executing fora month having 30 days(Like June,April..) and end date will come in such month which has 31 days(Like October,December...).

  I have used below SSRS expression :

  Last day of Month : DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Today), Month(Today), 1))))

  Next N Months end Date : DateAdd("m",N, DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Today), Month(Today), 1)))))

  N=1,2,3..

  Example where I failed :

  Current Month = JUNE(a month having 30 days)

  find Next 4 months end date = 30/10/2013

  But it should be 31/10/2013.

  Please send me if anyone have solution for this.

  Thanks in advance.

  --------------------------------------------------------------------------------

• Daniel Bowlin

  SSC-Dedicated

  Points: 34566

  More actions

  June 20, 2013 at 7:25 am

  #1625898

  Try this

  DATEADD("dd",-1,DATEADD("mm",DATEDIFF("mm","1901-01-01",Today())+5,"1901-01-01"))