Learn T-SQL With Erik: DATEDIFF Precision
Chapters
- 00:00:00 – Introduction
- 00:01:27 – Data Problems Overview
- 00:03:04 – Date Math Issues
- 00:04:18 – Indexing and Design Considerations
- 00:05:04 – Precision in Date Calculations
- 00:06:22 – Example Query Differences
- 00:08:44 – Conclusion
Full Transcript
Erik Darling here, with Darling Data, continuing somehow to record these videos despite the terrible heatwave that is upon us. This is not for the fragile. This is the second European mass die-off happening currently. Well, I mean, I’m in New York with air conditioning. Still not fun.
We’ve been state mandated to not have our air conditioners go above 78 degrees, but I don’t know. I’m out here breaking all sorts of rules. Anyway, in this video we’re going to talk about two problems that you will face when you need…
This is an introduction to the problem. We’re going to flesh more of the problem out in tomorrow’s video. But we’re going to talk about two of the things that you will run into issues with when you start trying to figure out the date distance between two columns.
And there are some matters of precision that need discussing. So, down in the video description, you may notice that this is part of a larger course called Learn T-SQL with Eric. You are seeing little sniffs and dribs and drops and drabs of the full course.
But you can purchase the full course for $100 off down below. You can do that. You can find the will and the power to purchase all the material.
And then you too can know as much about T-SQL and performance tuning as I do. You’re fully up to snuff on everything if you ever watch it. Purchasing is half the battle.
Watching is the other 100% of the battle. There are also other links down there. If you want to hire me as a consultant, you can do that. If you want to become a supporting member of the channel because you’re like, wow, Eric sure does give us a lot of free stuff.
I wouldn’t mind giving him four bucks a month. You can do that. You can also ask me office hours questions for free. And as always, please do like, subscribe, tell all your friends. Join monitoring mobile party.
Anyway, speaking of monitoring, important stuff out here. My free open source monitoring tool available up on GitHub. This link and other important links where you can acquire.
This free thing are down below. Totally free, totally open source. You don’t have to give me anything, pay me anything. I don’t look at your data because you’re not paying me.
If you were, it might be different. But it captures all the stuff that I look at and care about as a performance tuning expert in the world. So if you would like to get your hands on that, you can do that.
There are all sorts of great things that go along with that monitoring tool. But let’s talk about it. Let’s talk about our data problems here.
So we’ve talked about local variables. We’ve talked about date functions a little bit generally. In part two, we’re going to talk about things you might want to think about when you’re doing cross column date math. So we talked about like if you have a variable or a parameter, the thing that a lot of people mess up is they do the date math on the column and compare it to the parameter or variable.
We know we don’t do that. Flip that math because that’s where it’s better. I’ve created a couple indexes here just to sort of illustrate the problem a little bit.
One on last activity date, creation date. One on creation date, last activity date. And the point that I want to make here is that no matter what we do when we run this query, SQL Server has no choice but to fully scan that index because SQL Server has no way of calculating this currently, right? It has to evaluate every single row that goes in.
And SQL Server doesn’t keep any information about like, oh, this date is five days from this date or anything like that. Nothing like that is stored. These are like because SQL Server by default doesn’t like know what you’re going to care about with these two date columns.
So it’s just like, oh, see what they do. But so it’s up to you to figure out what you care about with these date columns. And it’s up to you to create computed columns that match the things that you care about.
But just on its own, the thing that’s important is that SQL Server has no idea what you care about. Okay. you will care about in the future right sql server does not create indexes for you aside from meager index pools and sql server does not pre-compute any things like this it is up to you to design your database in a way that gives you sufficient access to your data the very ivory polished way of saying this is that one should query data the way that it is stored and one should store data the way that it is queried so if we need to if this is a persistent calculation we might want to store this calculation some we’ll talk more about that tomorrow today’s video uh one thing that i think a lot of people sort of misunderstand is precision when it comes to this uh we talked i think we talked about this a little bit before but it’s worth repeating because most people do not spend much time thinking about this i’m going to run these two queries together they were made to be run together of course and uh we’re going to look at the difference here so this one is uh simply calculating the date diff in years where the difference is greater than or equal to 10. all right and this one is using a much higher precision this one is taking 10 years worth of seconds all right that’s a crazy number and saying give only give me these and what we’re going to see is that there is a big difference and uh just what what is a 10-year gap to date diff and what is this you know a big number of seconds gap right because that is much more precise right we’re going 10 years down to the second not just 10 years down to the year so when we look at the results in here this first result returns 643 rows and this second result returns 23 rows so there are only 23 out of the 643 rows that actually are 10 years down to the second that’s a very very big difference when you’re calculating these things and what i think you might notice here is uh if we look at the first result we will see that there is in fact a 10-year difference between all of these but the number of months even does not add up to 10 years worth of months precisely right we have 109 month difference until we scroll way down and then we finally get to a 120 and 120 month difference and the the day difference is also consistently just continuing to sort of rack up up as we as we look down through the results for the second result again only getting 23 rows this is down to the second 10-year difference notice that we start at the 120 month difference here and we start at the three thousand thirty six hundred well the even number would be uh three thousand six hundred and fifty right ten times 365 days or so right uh screw a leap year even though there’s maybe two of them in there i don’t know maybe one depending on if you get lucky but uh no there’s definitely two in there just where those two follow will be up to the gods uh but this starts right at where we would expect it to so this is down to the second and this is just down to 10 years right the two like the the date part has 10 years between one and the other but the number of months and days and all the other stuff may not be precisely 10 years so be very careful with how you frame your date stuff because you might have some surprising results in there and i think the the truly shocking thing to me is that a lot of the time when people um start seeing strange results in the date math they rather than up the precision of what of what the date diff is calculating they’ll start doing all they’re still adding and converting uh they’re they’ll start doing all sorts of strange date math on the columns to make it conform rather than just using a more precise date diff calculation it’s a very very strange where developer brains go in these things me i go that’s what i care about even in the heat where my brain is melting right so if we do care about 10-year precision then we would want to make sure that we are using the most precise measurement right all right thank you for watching i hope you enjoyed yourselves i hope you learned something i will see you in tomorrow’s video where we will talk further about the performance ramifications of these calculations and how you can avoid ramifications all right thank you for watching
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. Blog readers get 25% off the Everything Bundle — over 100 hours of performance tuning content. Need hands-on help? I offer consulting engagements from targeted investigations to ongoing retainers. Want a quick sanity check before committing to a full engagement? Schedule a call — no commitment required.