First and Last day of the month in a given date range

• ArjunaReddy

  Default port

  Points: 1467

  More actions

  June 26, 2014 at 12:36 am

  #287162

  Hi,

  I would like to get the first and last day of any month in a given date range.

  Ex: Display the first and last day of the months between @startDate and @EndDates.

  Input Params= @StartDate='2016-06-21 16:57:11.093'

  @EndDate = '2016-09-30 00:00:00.000'

  OutPut should be:-

  MonthStartDateMonthEndDate

  1/06/201630/06/2016

  1/07/201631/07/2016

  1/08/201631/08/2016

  1/09/201630/09/2016

  Please help me with the query.

  Thanks,

  Nagarjun.

• Dohsan

  SSCrazy

  Points: 2972

  More actions

  June 26, 2014 at 2:11 am

  #1724941

  As a caveat this is pre-first morning tea, but should work.

  DECLARE @StartDate DATETIME = '2016-06-21 16:57:11.093',

  @EndDate DATETIME = '2016-09-30 00:00:00.000';

  SELECTMonthStartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + N,0),

  MonthEndDate = DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate)+ N + 1, 0)-1

  FROMdbo.GetNums(0,DATEDIFF(MONTH,@StartDate,@EndDate));

  dbo.GetNums is a numbers function that you should be able to find on this forum