Randolph West continues a series on covering dates and times, looking at DATEDIFF and DATEDIFF_BIG:

The only functional difference between them is that the DATEDIFF_BIG() returns values as a BIGINT, for results that exceed the boundary of an INT. Keep this in mind when deciding which one to use. For example, the maximum number of seconds an INT can hold is 68 years, while a BIGINT can comfortably store the number of seconds in 10,000 years. This becomes especially important when dealing with microseconds and nanoseconds.

The rest of the post will use DATEDIFF() to refer to both functions.

I think this might be the first time I’d read about DATEDIFF_BIG()and I’m not aware of ever having used it.  But hey, it could make sense if you need to track more than 2 billion microseconds.

Related Posts

Identity Inserts: One Table at a Time

Bert Wagner shows that you can only insert with IDENTITY_INSERT = ON for one table at a time: Ok, simple enough to fix: we just need to do what the error message says and SET IDENTITY_INSERT ON for both tables: SET IDENTITY_INSERT dbo.User_DEV ON; SET IDENTITY_INSERT dbo.StupidQuestions_DEV ON; And… it still didn’t work: IDENTITY_INSERT is […]

Read More

Using APPLY to Reduce Function Calls

Erik Darling shows a clever use of the APPLY operator: A while back, Jonathan Kehayias blogged about a way to speed up UDFs that might see NULL input. Which is great, if your functions see NULL inputs. But what if… What if they don’t? And what if they’re in your WHERE clause? And what if they’re in […]

Read More


November 2018
« Oct Dec »