graham.wade 69740 wrote:
Wondering why you are not using the EOMONTH function for the last day of the relevant months?
For what was written as code in the article, I'm wondering the same thing. Unfortunately, the code in the article expresses the wrong method to do such date calculations because the WHERE clause in the reporting code uses BETWEEN. It means that, if someone writes similar code against a DATETIME2(7) column, the last 3 milliseconds of the day will be missed and a whole lot can happen in those 3 milliseconds that can cause substantial transactions to be missed throwing totals off by quite a bit. Both EOMONTH and the use of BETWEEN, IMHO, should be outlawed as a "Worst Practice".
The "Best Practice" would be to have used the standard "closed/open" temporal boundaries in the following form...
WHERE SomeDateTimeColumn >= CURRENTPeriodStart AND SomeDateColumn < NEXTPeriodEnd
... where the period start and ends are "whole" dates with a "midnight time".
The code that wasn't changed in the example was the code that most desperately needed to change.
I'm also dead set against having to maintain such a lookup table because you can't control when someone or something is going to run a report and the table may have been updated too early or too late and, again, milliseconds DO matter in this area. The view (actually, I'd make it an iTVF so you could vary the "today" date if needed... that won't work in a view) that Norty303 suggested would be more timely and require no maintenance.aya
And, no... the common justification for using BETWEEN because, supposedly, only whole dates will be used in the tables doesn't hold water because you simply cannot control what may happen to that table in the future. Someone may suddenly decide that the datatype and the data needs to change to something more finite. You also can't control what some desperate programmer may copy your code for. Use the standard that I cited above ALL the time and your code will be bullet-proof in those areas.
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.
"Change is inevitable... change for the better is not".
"Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)