The following formula will do it for you.
1. It finds the first Sunday on or previous to the given date (-53684 is the date serial number for Sunday, 07 Jan 1753).
2. It adds 6 days to that to find the Saturday following the given date.
3. If figures out what day of the year is for that Saturday and subtracts one from that to make it "zero based".
4. It divides that by 7 to come up with a zero based week number.
5. It adds one to that to return the one based week number.
The code will not work (silent failure to calculate the correct number) for any dates prior to 07 Jan 1753 nor for any dates in the final week of the year 9999.
is pronounced "ree-bar
" and is a "Modenism
" for R
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.
"If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
"Change is inevitable... change for the better is not."
When you put the right degree of spin on it, the number 3|8
is also a glyph that describes the nature of a DBAs job. 😉
How to post code problems
Create a Tally Function (fnTally)