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

Pivoting Performance Counter Data

Dave Bland shows how you can build a dynamic pivot to see performance counter data over a stretch of time: The next step is to write the code to capture the counter values and insert the data it the temporary table created above.  Because we need to capture the values over a period of time, […]

Read More

Near-Zero Downtime Identity Column Changes

I’m getting close to the end of my series on near-zero downtime deployments. This latest post involves identity column changes: There are some tables where you create an identity value and expect to cycle through data. An example for this might be a queue table, where the data isn’t expected to live permanently but it […]

Read More


November 2018
« Oct Dec »