Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

First and Last day of the month in a given date range Expand / Collapse
Author
Message
Posted Thursday, June 26, 2014 12:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, September 22, 2014 3:57 AM
Points: 45, Visits: 538
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:-

MonthStartDate MonthEndDate
1/06/2016 30/06/2016
1/07/2016 31/07/2016
1/08/2016 31/08/2016
1/09/2016 30/09/2016


Please help me with the query.

Thanks,
Nagarjun.
Post #1586240
Posted Thursday, June 26, 2014 2:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: 2 days ago @ 6:35 AM
Points: 356, Visits: 2,659
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';


SELECT MonthStartDate = DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDate) + N,0),
MonthEndDate = DATEADD(MONTH,DATEDIFF(MONTH,0,@StartDate)+ N + 1, 0)-1
FROM dbo.GetNums(0,DATEDIFF(MONTH,@StartDate,@EndDate));

dbo.GetNums is a numbers function that you should be able to find on this forum
Post #1586276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse