How to Find the Start and End Dates for the Current Week (and more)
-
Comments posted to this topic are about the item How to Find the Start and End Dates for the Current Week (and more)
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
(Sigh...) My apologies, folks. I spent a couple of hours manually color coding all of the code because I knew the "code windows" on this site makes a bit of wreck color wise. Apparently the site software finds code and automatically encapsulates it as a code window.
I had also spent some time making links bold, blue, underlined and on a separate line. The formatter took a bite out of those, as well, even though the "preview" authors get to see made no such changes.
I also missed a couple of word errors. For example, there's one place where I meant to type "accountant" and just typed the word "account". There are a couple of places I did that and dropped the word "of", etc. Please excuse my typing.
Anyway, all of that not withstanding, that you for the read and thank you for being a part of this wonderful community.
_____________________________________________________________________________________
Edit: I'm rereading the article and I can't believe the error I missed. Here the line of text before and after a graphic in the article and the graphic itself...
And here are the results for that run… same as if we used a hard-coded date of '0001-01-07', which is a SUNDAY.
The Bonus
I mistakenly pasted the graphic above for Monday. Here's the correct graphic for Sunday. I hope I can get Steve to make the correction for me.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Excellent article - thanks!
-
Thanks for the feedback, Kazoo! I appreciate it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Truly excellent article, as always. Thank you Jeff.
While working on the financial calendar suitable for retailers several years ago (whole weeks, 4-5-4, the year begins in first week of February of course as the retail industry cannot afford January as the start month of the year), I noticed that the ISO calendars related documentation mentions the "Thursday wins" rule for edge cases (translates to "Wednesday wins" rule for retailers). It would be so much nicer if they restated it as "Middle of the week wins", so it is applicable to any first day of the week rule.
Sorry to mention this, but there is a small typo in the part where you discuss the "closed/open" criteria as the best practice. The comment in the following line of code might need to be modified to read "-- SoW = Start of Week, SnW = Start of NEXT Week" because it now reads End of NEXT week instead of Start of NEXT week:
WHERE SomeDate >= SoW AND SomeDate < SnW -- SoW = Start of Week, SnW = End of NEXT Week
Oleg
-
Thanks for the feedback, especially on the experiences you've had in the past and especially about the "Middle of Week" thing.
Of course, many thanks for the catch on the typo. I'm going through an making a list and will submit it to the site as requested corrections.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Fantastic article Jeff.
Really appreciate the time and effort you put into explaining the code.
-
Awesome feedback, Tom. Thank you both for the read and the feedback. I really appreciate it.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I read the "tl:dr" before I saw your name, Jeff, and I knew this article was yours, lol. Looking forward to reading it later but had to post this now because it made me laugh. I always appreciate a Jeff Moden article, especially because of the depth!
For the uninitiated, be sure to check out Jeff's Tally OH and the priceless Tally Tables; two critically important articles that greatly improve the life of a SQL DBA. For maximum effect, be sure to make some beersicles beforehand.
Teaser: You'll finally understand what RBAR is and why Jeff's avatar has a SLASH through those letters. I once helped a programmer change a 24+ hour weekend query to a 3-minute query by addressing his RBAR (I know it could run faster but he was perfectly content with 3 min)! Also, I had a 30+ minute query (growing every month) and after using a Tally Table, it runs in 1.5 seconds and still does, all these years later. Thanks, Jeff!
-
I'm truly humbled by your thoughtful comments and the references to a couple of articles. I really appreciate the feedback and... I'm glad I could make you laugh! 😀 I'm also glad that those other things were able to help you in the past. From the performance changes you noted, you made a huge difference! Very well done!
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Truly brilliant.
By trial and error I was able to add absolute week number to my version of Calendar Table. Detailed explanation beats trial and error on any day.
Again, brilliant. Any chance Jeff would write a book? My library is full of solutions to common and not so common problems provided by Jeff. You may be younger than me, but you are my hero.
🙂
Zidar's Theorem: The best code is no code at all...
-
That's incredibly kind feedback, Zidar. Thank you very much. I truly appreciate it and, like any author, feedback like yours and the others on this thread helps keep us going.
Shifting gears a bit, I tend to be a bit of an iconaoclast when it comes to certain things in SQL Server. I don't know if you've seen it but, in perhaps the most heterodoxic presentation about SQL Server you'll ever see, I have a presentation where I destroy the myth of Random GUID fragmentation, apply lessons learned there to other types of indexes, and lay waste to the supposed "Best Practices" of index maintenance that we've all been mistakenly following for more than 20 years. The hint is that what people have taken to be "Best Practices" in the area of Index Maintenance were never intended to be "Best Practices" and I prove it as well as the damage we've inadvertently have been doing to our indexes.
Here's the link to that presentation on YouTube... I do have a couple of "mis-speaks" documented in the comments on that page... I recommend reading those first. The reason for those mis-speaks is that I was given the presentation to Europe section of a GROUPBY event and it was way too early for me. I didn't even have a pulse yet. 😀
https://www.youtube.com/watch?v=rvZwMNJxqVo
And make sure you watch past the Q'n'A at the end for a real shocker.
Also, are you sure that I'm younger than you? 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Another excellent article. I am currently working on a budgeting application in Oracle and decided to use these concepts in Oracle. WARNING: For anyone else wanting to use these concepts for use in Oracle, they claim they use a Julian calendar. Turns out Oracle does not take into consideration the change from Julian to Gregorian and ignores the days skipped in 1752. September 3 to the 13, 1752 do not exist but Oracle assumes they do and as a result, January 1, 0001 is a Saturday. As a precaution, I am using Monday, September 18, 1752 as my base date in case Oracle goes back and fixes this.
- hmbacon wrote:
Another excellent article. I am currently working on a budgeting application in Oracle and decided to use these concepts in Oracle. WARNING: For anyone else wanting to use these concepts for use in Oracle, they claim they use a Julian calendar. Turns out Oracle does not take into consideration the change from Julian to Gregorian and ignores the days skipped in 1752. September 3 to the 13, 1752 do not exist but Oracle assumes they do and as a result, January 1, 0001 is a Saturday. As a precaution, I am using Monday, September 18, 1752 as my base date in case Oracle goes back and fixes this.
Thanks for the feedback, @hmbacon. And, yeah... I know a lot of people that know about the Julian to Gregorian change but I don't know a lot of others that know things like you posted nor that it didn't actually happen all at once. I've submitted an article about doing UNIX Timestamp conversions and told folks that it's NOT equal to UTC time because of things like "Leap Seconds" and gave them a link if they're interested in that.
Fun stuff this calendar stuff especially when you add MS induce nuances like DATEDIFF(wk) always being based on Sunday week starts. The blows a whole lot of people away.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply