weekstart and weekend for a month in sql

Btw.., Weekstarts on Monday & ends on Sunday.

Define for us 'weeks', e.g. Saturday to Sunday, 1st to 7th, etc, custom logic based on month and year, etc.

I have an article out there called Build your own SQL calendar table to perform complex date expressions that has calendar_week_in_month, calendar_week_in_year columns already defined.

I'd avoid the complexity and kludginess of a calendar table for these types of simple date calcs.  The only actual date calculation required is the simple one for the first Monday of the month; every date from then on can be derived from that one date.


DECLARE @Month tinyint
DECLARE @Year smallint

SET @Month = 5
SET @Year = 2015

SELECT
    first_monday_of_month AS Wk1StartDate,
    DATEADD(DAY,  6, first_monday_of_month) AS WK1EndDate,
    DATEADD(DAY,  7, first_monday_of_month) AS Wk2StartDate,
    DATEADD(DAY, 13, first_monday_of_month) AS WK2EndDate,
    DATEADD(DAY, 14, first_monday_of_month) AS Wk3StartDate,
    DATEADD(DAY, 20, first_monday_of_month) AS WK3EndDate,
    DATEADD(DAY, 21, first_monday_of_month) AS Wk4StartDate,
    DATEADD(DAY, 27, first_monday_of_month) AS WK4EndDate,
    CASE WHEN DATEADD(DAY, 28, first_monday_of_month) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, 28, first_monday_of_month)
         ELSE '19000101'
         END AS Wk5StartDate,
    CASE WHEN DATEADD(DAY, 28, first_monday_of_month) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, 34, first_monday_of_month)
         ELSE '19000101'
         END AS Wk5EndDate
FROM (
    SELECT CAST(CAST(@Year AS char(4)) + RIGHT('0' + CAST(@Month AS varchar(2)), 2) + '01' AS date) AS first_day_of_month
) AS input_values
CROSS APPLY (
    SELECT DATEADD(DAY, -(DATEDIFF(DAY, 0, DATEADD(DAY, 6, first_day_of_month)) % 7), DATEADD(DAY, 6, first_day_of_month)) AS first_monday_of_month
) AS assign_alias_names

@ScottPletcher - your query works like a charm. Although, requesting a slight modification. Wk1start date should always be 1st day of the month, irrespective of the day & rest everything is good.

try

IF OBJECT_ID('tempdb..#Calendar') is NOT NULL
	Drop table #Calendar
GO

Declare @Start datetime, @End Datetime,@inc int, @Max int
,@MovingDate Datetime

select  @Start = dateadd(mm,datediff(mm,0,getdate()),0) ,
        @End   =     dateadd(dd,30,dateadd(mm,datediff(mm,0,getdate()),0)) 

Set @inc = 1
Set @MovingDate = @Start 

Select @Start, @End, @MovingDate

Create Table #Calendar(
EntryID int identity,
WeekStartdate datetime,
WeekEndDate datetime
)

While(@MovingDate <= @End)
BEGIN

		IF((SELECT DATEPART(dw,@MovingDate ))=1)
			Begin
					Insert into #Calendar(WeekStartdate)
					Select @MovingDate

					Update c
					Set WeekEndDate = dateadd(day,7,@MovingDate)
					from #Calendar c
					where EntryID = @inc

			Set @inc = @inc +1

			END

Set @MovingDate = dateadd(day,1,@MovingDate)

END

Select * from #Calendar

Select allOpen in new window

>> Wk1start date should always be 1st day of the month, irrespective of the day & rest everything is good. <<

Hmm.  So for May 2015, should it be like this?:
Wk1 May01 to May03
Wk2 May04 to May10
Wk3 May11 to May17
...

Or like this?:
Wk1 May01 to May07
Wk2 May08 to May14
Wk3 May15 to May21
...

try this one adjusted for your last requirement

IF OBJECT_ID('tempdb..#Calendar') is NOT NULL
	Drop table #Calendar
GO

Declare @Start datetime, @End Datetime,@inc int, @Max int
,@MovingDate Datetime

select  @Start = dateadd(mm,datediff(mm,0,getdate()),0) ,
        @End   =     dateadd(dd,30,dateadd(mm,datediff(mm,0,getdate()),0)) 

Set @inc = 1
Set @MovingDate = @Start 

Select @Start, @End, @MovingDate

Create Table #Calendar(
EntryID int identity,
WeekStartdate datetime,
WeekEndDate datetime
)

Insert into #Calendar(WeekStartdate)
Select @MovingDate

While(@MovingDate <= @End)
BEGIN

Print 'HI'
		IF((SELECT DATEPART(dw,@MovingDate) )=1)
			Begin
					Update c
					Set WeekEndDate = @MovingDate
					from #Calendar c
					where EntryID = @inc

			Set @inc = @inc +1
			END

		IF((SELECT DATEPART(dw,@MovingDate) )=2)
			Begin

				Insert into #Calendar(WeekStartdate)
				Select @MovingDate
			
				Update c
				Set WeekEndDate = Dateadd(day,6,@MovingDate)
				from #Calendar c
				where EntryID = @inc

			
			END

--Select @MovingDate
Set @MovingDate = dateadd(day,1,@MovingDate)

END

Select * from #Calendar

Select allOpen in new window

Scott -

yes,

Ex for May 2015

Wk1 May01 to May03
Wk2 May04 to May10
Wk3 May11 to May17
Wk4 May18 to May24
Wk5 May25 to May31

and for June
WK1 Jun01 to Jun07
Wk2 Jun08 to Jun14
Wk3 Jun15 to Jun21
Wk4 Jun22 to Jun28
Wk5 Jun29 to Jun30

so, Wk1 start date should always be 1st day of the month and Wk end of the last week should be last day of the month.

@Leo - Your query also works for the 1st day of the week however it doesnt end in the last day of the month. and it provides 2 different select queries as answer. i only want the EntryID, WeekStartDate & WeekEndate

@Leo - Nevermind of my 2nd question of 2 queries, I've commented this
--Select @Start, @End, @MovingDate
now im getting only the values i Want, however please check on the last day of the week when month end arrives.

DECLARE @Month tinyint
DECLARE @Year smallint

SET @Month = 6
SET @Year = 2015

SELECT
    first_day_of_month AS Wk1StartDate,
    Wk1EndDate,
    DATEADD(DAY,  1, Wk1EndDate) AS Wk2StartDate,
    DATEADD(DAY,  7, Wk1EndDate) AS Wk2EndDate,
    DATEADD(DAY,  8, Wk1EndDate) AS Wk3StartDate,
    DATEADD(DAY, 14, Wk1EndDate) AS Wk3EndDate,
    DATEADD(DAY, 15, Wk1EndDate) AS Wk4StartDate,
    CASE WHEN DATEADD(DAY, 21, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, 21, Wk1EndDate)
         ELSE DATEADD(DAY, -1, DATEADD(MONTH, 1, first_day_of_month))
         END AS Wk4EndDate,
    CASE WHEN DATEADD(DAY, 22, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, 22, Wk1EndDate)
         ELSE '19000101'
         END AS Wk5StartDate,
    CASE WHEN DATEADD(DAY, 22, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, -1, DATEADD(MONTH, 1, first_day_of_month))
         ELSE '19000101'
         END AS Wk5EndDate
FROM (
    SELECT CAST(CAST(@Year AS char(4)) + RIGHT('0' + CAST(@Month AS varchar(2)), 2) + '01' AS date) AS first_day_of_month
) AS input_values
CROSS APPLY (
    SELECT DATEADD(DAY, -(DATEDIFF(DAY, 0, DATEADD(DAY, 6, first_day_of_month)) % 7), DATEADD(DAY, 6, first_day_of_month)) AS first_monday_of_month
) AS assign_alias_names
CROSS APPLY (
    SELECT CASE WHEN first_day_of_month = first_monday_of_month
                THEN DATEADD(DAY, 6, first_day_of_month)
                ELSE DATEADD(DAY, -1, first_monday_of_month)
                END AS Wk1EndDate
) AS assign_alias_names2

ok here it is this should do it

try

--month start always at the beginnig
IF OBJECT_ID('tempdb..#Calendar') is NOT NULL
	Drop table #Calendar
GO




Declare @Start datetime, @End Datetime,@inc int, @Max int
,@MovingDate Datetime


Set @Start = '2015-05-01'
Set @End = '2015-05-31'


--select  @Start = dateadd(mm,datediff(mm,0,getdate()),0) ,
--        @End   =     dateadd(dd,30,dateadd(mm,datediff(mm,0,getdate()),0)) 

Set @inc = 1
Set @MovingDate = @Start 

--Select @Start, @End, @MovingDate

Create Table #Calendar(
EntryID int identity,
WeekStartdate datetime,
WeekEndDate datetime
)

Insert into #Calendar(WeekStartdate,WeekEndDate)
Select @MovingDate, DATEADD(wk, DATEDIFF(wk, 6, @MovingDate), 6 + 7)

Set @MovingDate = dateadd(day,1,@MovingDate)

While(@MovingDate <= @End)
BEGIN
	

	IF(( SELECT DATEPART(dw,@MovingDate) ) = 2 )
	Begin
		insert into #Calendar(WeekStartdate,WeekEndDate)
		Select @MovingDate, DATEADD(wk, DATEDIFF(wk, 6, @MovingDate), 6 + 7)
	END

	IF((select DATEADD(wk, DATEDIFF(wk, 6, @MovingDate), 6 + 7)) > @END)
	BEGIN
			Update c
			SET WeekEndDate = @End
			from #Calendar c
			Where WeekEndDate = (Select Max(WeekEndDate) from #Calendar )

	END

Set @MovingDate = dateadd(day,1,@MovingDate)

END


Select * from #Calendar

Select allOpen in new window

Perfect. Thanks a ton.

@Leo - you query was also equally good. However check the result for February month. It gives 2015-02-08  as Weekend date for both Week 1 & 2. Hence in the interest of time & quality, I chose Scott's response, which by the way, is mind blowing.

Thanks again experts.

Dog it. I was in a meeting

Nice work Scott. beat me to the punch.

I fixed your issue
here it is if you wan it in a table instead of rows
try

--month start always at the beginnig
IF OBJECT_ID('tempdb..#Calendar') is NOT NULL
	Drop table #Calendar
GO


Declare @Start datetime, @End Datetime,@inc int, @Max int
,@MovingDate Datetime

--Set @Start = '2015-08-01'
--Set @End = '2015-08-31'

Set @Start = '2016-02-01'
Set @End = '2016-02-29'

Set @inc = 1
Set @MovingDate = @Start 


Create Table #Calendar(
EntryID int identity,
WeekStartdate datetime,
WeekEndDate datetime
)

Insert into #Calendar(WeekStartdate,WeekEndDate)
Select @MovingDate, 
Case 
	when (dateadd(mm,datediff(mm,0,@MovingDate-1),0) ) = @MovingDate Then @MovingDate
	Else DATEADD(wk, DATEDIFF(wk, 6, @MovingDate), 6 + 7)
End


Set @MovingDate = dateadd(day,1,@MovingDate)

While(@MovingDate <= @End)
BEGIN
	

	IF(( SELECT DATEPART(dw,@MovingDate) ) = 2 )
	Begin
		insert into #Calendar(WeekStartdate,WeekEndDate)
		Select @MovingDate, DATEADD(wk, DATEDIFF(wk, 6, @MovingDate), 6 + 7)
	END

	
	IF( (Select dateadd(dd,-1,dateadd(mm,datediff(mm,0,@MovingDate+1),0)) ) = @MovingDate)
	BEGIN
	
			Update c
			SET WeekEndDate = @End
			from #Calendar c
			Where EntryID = (Select Max(EntryID) from #Calendar )

	END

Set @MovingDate = dateadd(day,1,@MovingDate)

END


Select * from #Calendar

Select allOpen in new window