June 20, 2018 at 2:11 pm
Hello all,
Thanks in advance for any assistance you can give me.
What I am trying to get is the first date in the current month based off Monday to Sunday work week. So for this month the first Date would be Monday 05/28/2018. The last day of this month would be Sunday 07/01/2018.
Thanks,
James Bosco
June 20, 2018 at 2:24 pm
James Bosco - Wednesday, June 20, 2018 2:11 PMHello all,Thanks in advance for any assistance you can give me.
What I am trying to get is the first date in the current month based off Monday to Sunday work week. So for this month the first Date would be Monday 05/28/2018. The last day of this month would be Sunday 07/01/2018.
Thanks,
James Bosco
What defines what month does a week belongs to?
Would last month last day be 05/27/2018?
June 20, 2018 at 2:31 pm
I have a tour calendar for June and a Tour date.
So for the first work week Monday for June would start on 05/28/2018,
The last work week Sunday would be 07/01/2018
I am attaching a calendar.
June 20, 2018 at 2:47 pm
James Bosco - Wednesday, June 20, 2018 2:31 PMI have a tour calendar for June and a Tour date.So for the first work week Monday for June would start on 05/28/2018,
The last work week Sunday would be 07/01/2018
I am attaching a calendar.
That's just repeating what you just said. It doesn't answer my questions. If you're provided with a calendar, build a calendar table that will help you to query those "calculated" dates.
June 20, 2018 at 2:56 pm
I am sorry. You are correct I could just do that. What I was wondering if I can do it based on today's date so it would be more dynamic so when the report is run for the next month it automatically selects the correct dates.
June 20, 2018 at 7:45 pm
James Bosco - Wednesday, June 20, 2018 2:56 PMI am sorry. You are correct I could just do that. What I was wondering if I can do it based on today's date so it would be more dynamic so when the report is run for the next month it automatically selects the correct dates.
You could calculate them, but you need to define the logic to define which week belongs to which month (and year).
June 20, 2018 at 10:09 pm
I'm stuck on the last Sunday at the moment but here's how you handle the first Monday.
Lynn Pettis has a great post about common date routines. I turned some of those routines into inline table valued functions. Here's a function that returns the first day of the month: CREATE FUNCTION dbo.firstOfMonth (@date datetime, @months smallint)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT monthStart = dateadd(mm,datediff(mm,0,@date)+@months,0);
GO
And one that returns the first business day of the week (Monday)CREATE FUNCTION dbo.firstOfWeek (@date datetime, @weeks smallint)
RETURNS TABLE WITH SCHEMABINDING AS RETURN
SELECT weekStart = dateadd(wk,datediff(wk,0,@date)+@weeks,0);
GO
To get the first Monday of the week of the month you can do this:SELECT firstMonday = w.weekStart
FROM dbo.firstOfMonth(GETDATE(), 0) m
CROSS APPLY dbo.firstOfWeek(m.monthStart, 0) w;
For this month the first Monday as 2018-05-28 which is what you were looking for.
Like I said, I'm a little stuck on the Sunday part (and out of time) but, this returns the correct answer (2018-07-01) for this month:SELECT w.weekStart-1
FROM dbo.firstOfMonth(GETDATE(), 1) m
CROSS APPLY dbo.firstOfWeek(m.monthStart, 0) w;
But for other months the correct calculation would be to replace w.weekStart-1 with w.weekStart+6. To push this across the finish line you can build a CASE statement which returns either w.weekStart-1 or w.weekStart+6 based on something I can't figure out at the moment.
-- Itzik Ben-Gan 2001
June 21, 2018 at 9:07 am
Thank you!! Thank you!! Thank you!!
June 21, 2018 at 9:22 am
I've got a possible "full monty" solution and explanation for this. I'll post it tonight after work.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 21, 2018 at 9:26 am
You guys all rock!!!
June 21, 2018 at 6:42 pm
The problem is that that your presumption on what the current month is is incorrect. If you go back to May, the 28th should be a part of May. In fact, June 1st and second should actually be a part of May because 4 or more days of the week are actually in May and not June.
The real rule for such a calendar is called the "ISO WEEK". The rule for the first week of the year is that week that contains the first Thursday of a year or, in a similar fashion, contains the 4th of January. Although not explicitly declared in the ISO standards, that also can (should) infer that the first week of any month is the week that contains the first Thursday of the month.
Most people can't afford to buy ISO Standards so here's a link to a Wikipedia article concerning ISO Weeks with mention of how months can be calculated even thou the ISO Standards (even through 2016) don't support the concept of "ISO Months".
https://en.wikipedia.org/wiki/ISO_week_date
Before we get into the ISO stuff, the ISO calendar function I created uses a function that takes the place of a "Tally or Numbers" table. Simply stated, it takes the place of a WHILE loop for counting. Here's an article that explains the concept.
http://www.sqlservercentral.com/articles/T-SQL/62867/
I've attached the "fnTally" function. Some documentation is included in the function code itself. Also, it's odd that an SQL Server forum such as this one won't allow you to upload files with the ".SQL" extension so I had to save it as a ".TXT" file. Just change the extension back to ".SQL" when you're ready to use it and everything will be fine.
Microsoft built an ISO function into SQL Server. Unfortunately, it's pretty much a crippled function because it only returns the ISO Week number and not the year. With that in mind, I built a couple of functions on my own. One can be found at the following URL. It's just as crippled as SQL Server's ISOWK DatePart and was meant only as an exact replacement for MS' function for those that didn't have a version of SQL Server that supported the ISOWK DatePart. However, the article is key in that it explains how to calculate the ISO Week without using a WHILE loop like MS did in previous versions of Books Online and that opens up a whole world of possibilities. Here's the link for that article.
http://www.sqlservercentral.com/articles/T-SQL/97910/
The other function I built takes two dates (start and end) and returns an iTVF (high performance Inline Table Valued Function). It really doesn't matter what the dates are because only the year from the dates is used. The only other rule is that the end date should be >= the start date. I've attached that function, as well.
Ok... so what does all that have to do with your problem? It turns out that ISO Weeks and the ISO Months (not actually supported by the ISO Standards) that can be derived from that fit your problem very nicely and consistently. The code also contains some documentation but here's how to use it to find the start and end dates of the "months" where the month always starts on a Monday and always ends on a Sunday.
--===== Return the start date and end date and other infor
-- for all ISO Months from 2010 thru 2020.
SELECT ISOYear
,ISOMonth
,MonthAbbreviation = LEFT(DATENAME(mm,DATEADD(mm,ISOMonth,-1)),3)
,MonthName = DATENAME(mm,DATEADD(mm,ISOMonth,-1))
,WeeksInMonth = MAX(ISOWeekOfMonth)
,MonthStartDT = MIN(WeekStart)
,MonthEndDT = MAX(NextWeekStart)-1
FROM dbo.IsoWeekCalendar('2010','2020')
GROUP BY ISOYear, ISOMonth
ORDER BY ISOYear, ISOMonth
;
--Jeff Moden
Change is inevitable... Change for the better is not.
June 22, 2018 at 2:40 pm
Thank you very much Jeff. You truly are amazing.
June 22, 2018 at 3:24 pm
Thanks for the feedback. Glad to help.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply