get week number of month
-
Yes thanks Jeff Moden I love it when I can tune into a forum and actually learn something new. Your expertise and your willingness to give it to others for free is greatly appreciated by all of us seeking knowledge either directly or indirectly and that I am 100% positive of this. This legacy you leave will assuredly live on.
- Phil Parkin wrote:GrassHopper wrote:
I had to look up "corpuscles", for a moment I thought it was a new drink at starbucks.
Thank you all for taking the time and giving me your take on how to tackle this issue. Much appreciated! Alex
And thank you for making me laugh with the Starbucks comment 🙂
BWAAA-HAAA-HAAA!!! Ditto that! It's a good thing I didn't have a mouthful of coffee when I read that or I'd have needed a new keyboard thanks to the major SPOM! 😀 Thanks for the laugh, Alex!
--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) - rjaye03 wrote:
SET DATEFIRST 6 ; -- Set first Day of Week to Saturday SELECT [CreateDateTime],DATEPART(WEEK,[CreateDateTime]) - DATEPART(WEEK,CAST(CAST(YEAR([CreateDateTime]) AS VARCHAR(4))+'-' + CAST(MONTH([CreateDateTime]) AS VARCHAR(2)) + '-01' AS DATETIME))+1 AS WeekNo FROM yourtable
I just want you to know something very important. There were probably hundreds of folks that looked at this post. Compare that likely fact with how many people provided an actual response. You are one of the good folks that tried to help, which is a very small minority!
Most of what I have learned is by trying to help others and either failing or having someone pointing out an ever better way. That's also why I love this particular forum. The opportunity for me to learn and then pass it forward either in the real work I do at work or to help others do theirs is an incredible opportunity that the community on this forum strongly supports.
Please don't ever lose your willingness to help. The world needs people like you.
And, with that, I'll share a meme that says it all (and, no... I'm not calling you a "beginner" because I'm still trying to not be a beginner myself 😉 ) ...
--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) - Dennis Jensen wrote:
Yes thanks Jeff Moden I love it when I can tune into a forum and actually learn something new. Your expertise and your willingness to give it to others for free is greatly appreciated by all of us seeking knowledge either directly or indirectly and that I am 100% positive of this. This legacy you leave will assuredly live on.
Good, Sir... I'm both humbled by and appreciative of your very kind words. I know this may seem odd but I suffer from the world's biggest case of Imposter Syndrome there is. It's good to sometimes get feedback that I've been able to help, like you, Phil, and Alex have provided.
As my dearly departed Dad used to say, "I aim to please... I sometimes miss, but I'm always aiming". 😀
--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) -
@alex (or anyone else that may be interested), since we're on the subject of "Week of the Month", here's some information and another function that might be of interest.
Note that there is a graphic that is incorrect in one of the "Sunday" examples. I have to work with Steve Jones and get that corrected but the code is solid as it is.
--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) -
Please do a little research. There is an ISO 8601 standard for week numbers within a year. It's very popular in Scandinavian countries, and you can find lots of calendars that include it so you don't have to do any computations at all. Build a calendar table using the DATE data type and add a week column. It also might be worth adding an ordinal date and a Julian eyes date for, say, 100 years to this table. As I recall the date is only three bytes, so any calendar table that you create will be pretty small. Since you know you're never going to add another day to it, your fill factor can be very tight. Remember that SQL is a database language and not a computational language.
Please post DDL and follow ANSI/ISO standards when asking for help.
-
I seriously agree about the ISO thing. It's a real shame that MS hasn't made a few more built in functions around ISO Week and Year.
I advise caution when using Calendar tables because, contrary to what Joe said, JOINs are frequently a whole lot slower than doing a minor calculation and the minor calculations also impart no extra I/O to do things with like JOINs would.
It would also be wonderful if most countries and industries adopted the ISO temporal standards. But, they won't because of supposed "Calendar Year" and "Calendar Month" requirements that even governments embrace. When you run into that type of thing, you have to be able to handle it and good ol' "non-computational" SQL handles it just fine if you know the gazintas and you know SQL.
--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 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply