Learn T-SQL With Erik: DATEDIFF Considerations

Posted on by Erik Darling

Learn T-SQL With Erik: DATEDIFF Considerations

Chapters

Full Transcript

Erik Darling here, with Darling Data, trying to enunciate so the robots stop transcribing my name, and stuff all weird. Stupid robots. I probably shouldn’t say that, they’re gonna transcribe that and get mad at me. Robot, if you can hear me, I apologize.

Anyway, in today’s video we are going to continue our learning journey through the T-SQL language. That’s how we talk to our SQL servers. And we’re going to talk about some considerations around date diff that I think are interesting.

This is of course just small snippets of the full course material, so if you are interested in going beyond what I’m talking about in these videos, I would encourage you to go down to the links below, where you will find a link to buy this entire course for $100 off the manufacturer suggested retail price. On your way there, all sorts of other great links.

You can hire me for consulting, you can become a supporting member of the channel for anywhere between $4 and $10 a month, if you feel so inclined. You can ask me office hours questions. And of course, another thing that I would encourage you to do.

Actually, I have two things that I would encourage you to do. One of them is of course to like, subscribe, and tell a friend. And the other one is to check out my free SQL Server performance monitoring tool. It’s all the stuff that I think is important to monitor for performance in SQL Server.

And it is all stuff that I think is pretty well thoughtfully laid out. I am working on some cool new features now that you should see bubbling up. And it’s just a real good time and a very fulfilling process building a popular community tool.

I’m over 11, close to 12,000 downloads at this point. So I’m pretty excited about that. And with all that stuff out of the way, let’s continue our summer journey here.

And let’s, of course, you know, what do you call it there? . Let’s talk about T-SQL.

That sounds like a good idea to me. All right. We’re going to go to Management Studio. And let me just do a little bit of cleanup over yonder here. So working with dates and times, if you’ve ever had a weird experience, like maybe you haven’t, you know, used the right matching data types, and you’ve had a performance issue, or you’ve hit weird bugs, there’s all sorts of stuff that should rightfully strike fear into the very hearts, minds, and souls of developers when they’re working with these things.

So we’re going to talk about just some not terribly advanced stuff, but stuff that is at least worth making sure that everyone understands when it comes to the date diff function.

There’s not terribly a lot of advanced things to say about it, but who knows where you’re starting off. So one thing that seems to get on some people’s nerves is deciding on a boundary.

So when you say, I only care about a year of data, you need to think carefully about how you do that. All right.

Do you just say date diff year minus 1? Do you say date diff month minus 12? Do you say date diff day minus 365? These things can all measure slightly different things. Depending on where you measure your boundaries.

Not so much for dates, of course, but if you have date times or date time twos, more precise data types than just a date, these things become quite important to think about.

You might even figure out how many seconds are in a year and measure that precisely if you need an absolute year from when your query runs. These are things that not a lot of people consider.

This stuff all becomes… I think much more interesting when you’re dealing with somewhat narrower spans of time, like is the last day of data just day minus 1? Is it 24 hours minus 1?

Is it however many minutes or seconds minus those numbers? You have to think about this stuff. Then I think one thing that is somewhat excruciating about date diff is just, I mean, it’s not like you weren’t warned, but if you look at these two dates, we have 2025, 1231, at 2359, 59, a bunch of 9s, and then we have 2026, 0101, and a bunch of 0s.

If we measure this very last moment of 2025 and compare it to the very first moment of 2026, there’s a whole lot of date diff boundaries that will be true and will come back with a 1.

If we… Let’s see. Let’s blow this up a little bit. The date diff between 2025, 1231, well, it says that’s a year apart, which technically it is.

We crossed a year boundary. It’s also one month apart. We crossed one month boundary. It’s also one day, one hour, one minute, one second, one millisecond, and one microsecond apart because we have crossed all of those boundaries, but the difference between all of them is, of course, 1.

And this is just stuff that you need to sort of be aware of. When you are using date diff to look at the differences between two things, the results can get a bit surprising to some people.

So you should always think quite carefully about which chasm you are attempting to span when you are date diffing.

The other thing that you can do that becomes interesting with both date add and date diff is flattening dates.

Again, not terribly advanced stuff, but there’s been cheat sheets and all sorts of stuff throughout the years published where people will tell you all different ways to figure out how to add some span of time to make sure that everything is working.

But SQL Server 2022, they put in this function called date trunk. And date trunk, look how nice and compact this is, right? We’re just going to truncate this to the last year, which beats the pants out of all of us.

All the other code that you would have to write in order to do this in the past. You would have to add a year to the date diff between the year and 1900 or 101, and then all this code, right?

It’s a lot of stuff. But the good news is date trunk makes life a lot easier, at least for truncating back to a date, right? So both of those pieces of code, this being far more succinct, give us the same thing.

We just go back to the first of the year of 2020. 2026. But getting to the end of a thing is a bit more challenging, right? So I actually opened up a support issue saying that it would be cool if date trunk accepted a third parameter where you could add whatever span of time you wanted to this.

So like, if this were date trunk year at test date time comma one, then we would add one year to test date time and go forward a year. We don’t have that currently. We may never have that because apparently fabric is more important than SQL Server.

But it is there and it does work pretty well. You can flatten with date trunk down to all sorts of wonderful things. But if you want to add time onto that, you still need to do a little bit of extra annoying stuff.

So what I’m doing here is adding one year to date trunk. Like if we had that third parameter, we could skip all this stuff and we could just say add one year to it and truncate to that, which would be so much more nice and compact.

But one thing that occurs to me is I don’t know that the people who make SQL Server actually use SQL Server. You got to keep bothering them for things.

You got to keep making feature requests and all sorts of other annoying stuff. But just to be extra precise here, 50 nanoseconds just rounds to 100 nanoseconds, right? 100 nanoseconds is the smallest unit of sort of measure that you can get out of these things.

But if you do this, then we will get us to our one year thing, right? So this will get us to the very end of 2026, 1231, right? We could have just added a year or something.

But if you just wanted to get to the very last moment of 2026, that’s how you would do it. If you just wanted to get to the first of 2027, well, of course, that’s a little bit less math, which is always a good thing.

More math and more problems, right? But you still need to think about the chasms of time that you care about. So three months, 12 weeks, 90 days, like what actual span of time do you care about?

It’s an important question because you might be either including data that you shouldn’t or not including data that you should. And these are important considerations when you are measuring times with these boundaries.

So if I run these queries, or I guess this is one query with just a bunch of things selected in it, we have what is right now, right?

So this will bring us back to the first of June, which is just about a week ago now, at least when I’m recording this. When this gets published is, of course, in the future. We have the right now date, which is how you would do this if you just wanted to convert this from a date time with all the stuff in it, or I guess that’s a date time too, technically, to just this is a date.

You can get to the end of the month with the EOMONTH function. Pretty handy thing there. We don’t have any other functions, right? It says EOMONTH. There’s no EODay, EOYear, right?

Can we just get to the end of the month? Sure. So this is the reason why I think that datetrunk should accept a third parameter is because EOMONTH does.

This is an example of adding three months to EOMONTH, and this is an example of subtracting three months from EOMONTH, and this just gets us some slightly different things. So the end of three months into the future would be September 30th, and the end of three months ago would be the end of March, right?

So EOMONTH has this neat third input that you can use, but datetrunk does not. So it would be nice if datetrunk did, right? That’d be good stuff.

Datetrunk returns a dynamic data type. So you do have to be careful with how you’re doing this, because if you put in a system function like sysdatetime that returns a datetime2 and you compare that to a datetime column, you may find yourself in rather awkward situations, both with data correctness, of course, and with, you know, like query performance can also be impacted, the query plans that SQL Server comes up with, the little optimizer rules like getRangeThroughConvert and getRangeThroughMismatchType and stuff, they often result in very awkward query plans where you have these, like, these constant scans and these merge things and then like an awful little nested loop into your table billions and billions of times.

It’s not fun. So we have to be very precise with data types. But we can use this describeFirstResultSet procedure, and what we’ll see is that in the first one where I am, where my input value here is a datetime2 and here where I’m converting that, or rather I’m converting this string to a datetime27 and this one where I’m converting it to a datetime, SQL Server will respect whatever you convert it to.

So that first one comes back as a datetime2 and the second one comes back as a datetime. So just be very, very careful because that is a dynamic data type. You can’t control it, but it is dynamic based on what goes into it.

Anyway, not a lot of fireworks in this one, but some good stuff to think about if you haven’t spent enough time thinking about working with dates. We’re going to look at some more interesting stuff tomorrow.

This is just, you know, some things that you have to say to clear the air first. All right. Anyway, thank you for watching. I hope you enjoyed yourselves. I hope you learned something.

I hope you will think quite carefully about what chasms of time you are hoping to span when you start date diffing and date adding. And I will see you over there. I will see you over in tomorrow’s video.

All right. Thank you. Goodbye.

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.

Related Posts