This is one excellent algorithm to get the ISO week number.
However doing this by using 17530101 instead of 19000101 throws an error
Arithmetic overflow error converting expression to data type datetime.
It turns out the highest integer value "DATEPART(DAYOFYEAR" can accept 2958463. Which is 8100 years, which accidentally is the number of years between 1900 and 9999 and number of days between 19000101 and 99991231. My guess is that a developer over at Microsoft has hardwired this upper limit.
Too funny and brilliant observation, Peter. Your CASE statement fix works great especially since I would imagine that most folks would encapsulate the formula in one form or another of a function.
Your fix for the earlier date does have me thinking about Integer Math and conversions, though. I don't know why, but I've never tested to see if the use of Date Serial Numbers such as 0 ('19000101') and -53690 ('17530101') are faster than their string counter parts as we use them in some of the date functions. We do know that CAST and CONVERT to strings and back again is slower but I've never tested the implicit conversions. I'll give it a whirl when I get home from work tonight.
Thanks for stopping by, Peter. Haven't seen you around much and it's a real pleasure to see that fabulous mind at work here, again.
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Helpful Links:
How to post code problemsHow to post performance problemsForum FAQs