weekstart and weekend for a month in sql

Team - I have 2 parameter (Month & Year). Both are int.

Ex:
Month - 5
Year - 2015

How can i get a all week start & end date's.

Ex: Wk1StartDate, WK1EndDate, Wk2StartDate, Wk2EndDate..., Wk5Enddate.

Catch is, it should calculate the number of weeks & display accordingly. For example, in February if we dont have WK5, then for both WK5start & EndDate, value should be '1900/01/01'

Help.
LVL 7
ManjuIT - Project ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ManjuIT - Project ManagerAuthor Commented:
Btw.., Weekstarts on Monday & ends on Sunday.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
Scott PletcherSenior DBACommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

ManjuIT - Project ManagerAuthor Commented:
@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.
0
Leo TorresSQL DeveloperCommented:
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

Open in new window

0
Scott PletcherSenior DBACommented:
>> 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
...
0
Leo TorresSQL DeveloperCommented:
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

Open in new window

0
ManjuIT - Project ManagerAuthor Commented:
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.
0
ManjuIT - Project ManagerAuthor Commented:
@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
0
ManjuIT - Project ManagerAuthor Commented:
@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.
0
Scott PletcherSenior DBACommented:
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
0
Leo TorresSQL DeveloperCommented:
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

Open in new window

0
Scott PletcherSenior DBACommented:
CORRECTIONs:

I had assumed there can't be a sixth week but there actually could be.  Naturally you can comment out the sixth week if you don't want to see it, but at least the code will be there if you do.  I also adjusted the week 5 calc, based on the fact that a sixth week could follow.


DECLARE @Month tinyint
DECLARE @Year smallint
SET @Month = 8
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 last_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 CASE WHEN DATEADD(DAY, 28, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
                   THEN DATEADD(DAY, 28, Wk1EndDate)
                   ELSE last_day_of_month
                   END
         ELSE '19000101'
         END AS Wk5EndDate,
    CASE WHEN DATEADD(DAY, 29, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
         THEN DATEADD(DAY, 29, Wk1EndDate)
         ELSE '19000101'
         END AS Wk6StartDate,
    CASE WHEN DATEADD(DAY, 29, Wk1EndDate) < DATEADD(MONTH, 1, first_day_of_month)
         THEN last_day_of_month
         ELSE '19000101'
         END AS Wk6EndDate
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,
        DATEADD(DAY, -1, DATEADD(MONTH, 1, first_day_of_month)) AS last_day_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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ManjuIT - Project ManagerAuthor Commented:
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.
0
Leo TorresSQL DeveloperCommented:
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

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.