T-SQL 101: 71 Subtracting dates and times in SQL Server T-SQL with DATEDIFF and DATEDIFF_BIG

When you’re writing T-SQL code, it’s also common to need to work out how far apart two dates are, or it could even be dates and times.

The DATEDIFF() function does that. In this case I’m asking how many days is it from 28th of February 2019 to 31st of July 2019?  The answer is:

So it says the difference is 153 days. Now we could have used any of those other intervals for that. I mentioned them before in this post.

I could work at how many minutes that was or how many seconds that was between the two times. I could even ask how many months it is.

The first date that we pass is the from date or the from starting point. The second one is the ending time. So if I put those around the other way it will also work it out backwards, but I would see -153.

Finally, it might occur to you that if I asked for nanoseconds over a long period, a very big number would come back. It could be too big for an integer. So to get around that, there’s also a DATEDIFF_BIG() function that does the same thing but returns a bigint instead.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2020-05-25