I DO agree that we have our own personal experiences to contend with. Ironically, I find that people that don't know how to use date math make some pretty complicated stuff that's actually a bit difficult to troubleshoot because of all the functions they've had to use.
A simple example would be to have someone generate a million row test table with random dates and times without using some form of direct temporal math especially if the range of dates/times need to be constrained. (Actually, I don't think that can be done without some form of direct date math... you do have to use a random number generator and constrain the values somewhere in that mix).
If you want to have some fun, create a million row table with each row having a reasonable StartDateTime and an EndDateTime where the span is random but less that a couple of years. Then ask them to calculate the total duration represented in the table and display it as HHHHHHHHHH:mi:ss:mmm in one column and decimal hours in another using SQL Server 2014 or less and without using any direct date math.
And, just to be sure, in no way, shape, or form am I suggesting that the temporal functions be totally avoided. In fact, life would be a bugger without them.
Shifting gears to a problem that we can probably both agree on, I have to fill out electronic time cards just like everyone else in the company does. The electronic time sheets accept only decimal hours and allows for up to 2 decimal places. I'm also required to be accurate because my time is expensive. I may finish 3 minor tasks (5 minutes each) for 3 different clients in just 15 minutes. But I can't track the time that finitely because the stupid computer driven time sheets will only accept quarter hour increments. For that 15 minutes, I have to charge 45 minutes of time, total. Then people dealing with the time cards ask if I really worked 15 hours for a given day for manpower planning purposes. <headdesk>
Or, I might work on a single task for a single client for 38 minutes but I have to enter the time as 45 minutes because it was longer than half of the quarter hour mark.
And, the fact that I might start at 38 minutes after the hour and end 17 minutes after the next hour means that I have to do the mental calculation of adding 22 minutes to 17 minutes to come up with 39 minutes and then have to remember to bump that up to 45 minutes because it, too, went past the halfway mark of a quarter hour.
Personally, I think all of that is pretty lame in this day and age .
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)